Posted in Azure, Data Lake, Date Lake Analytics, U-SQL

Epoch to DateTime in Date Lake Analytics

This blog post will describe the way to convert an Epoch time (Unix time) to a DateTime using a C# function and how you can use this in Azure Data Lake Analytics.
Unix time is defined as the number of seconds since midnight (UTC) on 1st January 1970.

The easiest way to work with Data Lake assemblies from Visual Studio is by installing the Data Lake Tools (see: https://docs.microsoft.com/en-us/azure/data-lake-analytics/data-lake-analytics-data-lake-tools-install).

Prerequisites
This blog assumes that you have a valid Azure subscription with at least the following resources running:
• Data Lake Storage
• Data Lake Analytics

.NET Class Library
First, we create a .NET class library which we later can use in Azure Data Lake Analytics. To do this follow the next steps.

  1. Create a new U-SQL Class Library (assuming Data Lake Tools are installed)
    01

  2. Create a class and add the following function:

     public static DateTime UnixTimeStampToDateTime(long unixTimeStamp)
 {
     DateTime dateTimeResult = 
         new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc);
     return dateTimeResult.AddSeconds(unixTimeStamp).ToLocalTime();
 }

Date Lake Analytics
The next step is to register the assembly in Data Lake Analytics.

  1. Create a database to save the created .NET assembly. To do this run a job with the following script:
CREATE DATABASE IF NOT EXISTS ReferenceDB;

  1. Deploying the library to Data Lake Analytics is easy using Visual Studio. Right click the project and select ‘Register’. Select your Data Lake Analytics account and select the database you created in the previous step.

  2. Submit the registration. After a successful registration your assembly is visible in the data explorer.
    02

At this point you’re ready to use the created function in a U-SQL job.
Create a new job and use the following query:

REFERENCE ASSEMBLY ReferenceDB.EpochConvert;
 
@result = 
SELECT *
FROM (VALUES 
     (1530003614,
     EpochConvert.EpochConvert.UnixTimeStampToDateTime(1530003614))) AS vt(ts, dt);

OUTPUT @result
TO "/Output/epochConversion.csv"
USING Outputters.Csv();

This query generates two columns. One with the timestamp value and the other one with the converted value. The result is saves to a CSV file in the Data Lake Storage. You can browse the output by the Data Explorer.

03