Aster Julia 2

aster-julia-2.jl

#Normalize timestamps from local time to UTC time
aster_view_string = "
create view temp.vw_airline_times_utc as 
select
row_number() over(order by flight_takeoff_datetime_origin) as unique_flight_number,
origin,
flight_takeoff_datetime_origin, 
flight_landing_datetime_origin,
flight_takeoff_datetime_origin - (INTERVAL '1 hour' * timezone) as flight_takeoff_datetime_utc,
flight_landing_datetime_origin - (INTERVAL '1 hour' * timezone) as flight_landing_datetime_utc,
timezone
from temp.airline
left join temp.airport on (airline.origin = airport.iatafaa);"

#Execute query
query(aster_view_string, asterconn)

#Teradata Aster SQL-H functionality, accessed via ODBC query
burst_query_string = 
"create table temp.airline_burst_hour distribute by hash (origin) as 
SELECT 
*,
\"INTERVAL_START\"::date as calendar_date,
extract(HOUR from \"INTERVAL_START\") as hour_utc
FROM BURST(
     ON (select 
        unique_flight_number, 
        origin,
        flight_takeoff_datetime_utc,
        flight_landing_datetime_utc
        FROM temp.vw_airline_times_utc
)
     START_COLUMN('flight_takeoff_datetime_utc')
     END_COLUMN('flight_landing_datetime_utc')
     BURST_INTERVAL('3600')
);"

#Execute query
query(burst_query_string, asterconn)