SQL-based ingestion query examples

    These example queries show you some of the things you can do when modifying queries for your use case. Copy the example queries into the Query view of the web console and run them to see what they do.

    This example inserts data into a table named without performing any data rollup:

    Show the query

    INSERT with rollup

    This example inserts data into a table named kttm_data and performs data rollup. This example implements the recommendations described in Rollup.

    1. INSERT INTO "kttm_rollup"
    3. WITH kttm_data AS (
    4. SELECT * FROM TABLE(
    5.   EXTERN(
    6.     '{"type":"http","uris":["https://static.imply.io/example-data/kttm-v2/kttm-v2-2019-08-25.json.gz"]}',
    7.     '{"type":"json"}',
    8.     '[{"name":"timestamp","type":"string"},{"name":"agent_category","type":"string"},{"name":"agent_type","type":"string"},{"name":"browser","type":"string"},{"name":"browser_version","type":"string"},{"name":"city","type":"string"},{"name":"continent","type":"string"},{"name":"country","type":"string"},{"name":"version","type":"string"},{"name":"event_type","type":"string"},{"name":"event_subtype","type":"string"},{"name":"loaded_image","type":"string"},{"name":"adblock_list","type":"string"},{"name":"forwarded_for","type":"string"},{"name":"language","type":"string"},{"name":"number","type":"long"},{"name":"os","type":"string"},{"name":"path","type":"string"},{"name":"platform","type":"string"},{"name":"referrer","type":"string"},{"name":"referrer_host","type":"string"},{"name":"region","type":"string"},{"name":"remote_address","type":"string"},{"name":"screen","type":"string"},{"name":"session","type":"string"},{"name":"session_length","type":"long"},{"name":"timezone","type":"string"},{"name":"timezone_offset","type":"long"},{"name":"window","type":"string"}]'
    9.   )
    10. ))
    12. SELECT
    13.   FLOOR(TIME_PARSE("timestamp") TO MINUTE) AS __time,
    14.   session,
    15.   agent_category,
    16.   agent_type,
    17.   browser,
    18.   browser_version,
    19.   MV_TO_ARRAY("language") AS "language", -- Multi-value string dimension
    20.   os,
    21.   city,
    22.   country,
    23.   forwarded_for AS ip_address,
    25.   COUNT(*) AS "cnt",
    26.   SUM(session_length) AS session_length,
    27.   APPROX_COUNT_DISTINCT_DS_HLL(event_type) AS unique_event_types
    28. FROM kttm_data
    29. WHERE os = 'iOS'
    30. GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
    31. PARTITIONED BY HOUR
    32. CLUSTERED BY browser, session

    This example aggregates data from a table named w000 and inserts the result into w002.

    Show the query

    INSERT with JOIN

    This example inserts data into a table named w003 and joins data from two sources:

    Show the query

    1. INSERT INTO w003
    2. WITH
    3. wikidata AS (SELECT * FROM TABLE(
    4.   EXTERN(
    5.     '{"type":"http","uris":["https://druid.apache.org/data/wikipedia.json.gz"]}',
    6.     '{"type":"json"}',
    7.     '[{"name":"isRobot","type":"string"},{"name":"channel","type":"string"},{"name":"timestamp","type":"string"},{"name":"flags","type":"string"},{"name":"isUnpatrolled","type":"string"},{"name":"page","type":"string"},{"name":"diffUrl","type":"string"},{"name":"added","type":"long"},{"name":"comment","type":"string"},{"name":"commentLength","type":"long"},{"name":"isNew","type":"string"},{"name":"isMinor","type":"string"},{"name":"delta","type":"long"},{"name":"isAnonymous","type":"string"},{"name":"user","type":"string"},{"name":"deltaBucket","type":"long"},{"name":"deleted","type":"long"},{"name":"namespace","type":"string"},{"name":"cityName","type":"string"},{"name":"countryName","type":"string"},{"name":"regionIsoCode","type":"string"},{"name":"metroCode","type":"long"},{"name":"countryIsoCode","type":"string"},{"name":"regionName","type":"string"}]'
    8.   )
    9. )),
    10. countries AS (SELECT * FROM TABLE(
    11.   EXTERN(
    12.     '{"type":"http","uris":["https://static.imply.io/example-data/lookup/countries.tsv"]}',
    13.     '{"type":"tsv","findColumnsFromHeader":true}',
    14.     '[{"name":"Country","type":"string"},{"name":"Capital","type":"string"},{"name":"ISO3","type":"string"},{"name":"ISO2","type":"string"}]'
    15.   )
    16. ))
    17. SELECT
    18.   TIME_PARSE("timestamp") AS __time,
    19.   isRobot,
    20.   channel,
    21.   flags,
    22.   isUnpatrolled,
    23.   page,
    24.   diffUrl,
    25.   added,
    26.   comment,
    27.   commentLength,
    28.   isNew,
    29.   isMinor,
    30.   delta,
    31.   isAnonymous,
    32.   user,
    33.   deltaBucket,
    34.   deleted,
    35.   namespace,
    36.   cityName,
    37.   countryName,
    38.   regionIsoCode,
    39.   metroCode,
    40.   countryIsoCode,
    41.   countries.Capital AS countryCapital,
    42.   regionName
    43. FROM wikidata
    44. LEFT JOIN countries ON wikidata.countryIsoCode = countries.ISO2
    45. PARTITIONED BY HOUR

    Show the query

    REPLACE for replacing a specific time segment

    This example replaces certain segments in a datasource with the new query data while dropping old segments:

    Show the query

    1. REPLACE INTO w007
    2. OVERWRITE WHERE __time >= TIMESTAMP '2019-08-25 02:00:00' AND __time < TIMESTAMP '2019-08-25 03:00:00'
    3.   FLOOR(__time TO MINUTE) AS __time,
    5.   countryIsoCode,
    6.   countryName,
    7.   regionIsoCode,
    8.   regionName,
    9.   page
    10. FROM w007
    11. WHERE __time >= TIMESTAMP '2019-08-25 02:00:00' AND __time < TIMESTAMP '2019-08-25 03:00:00' AND countryName = "Canada"
    12. PARTITIONED BY HOUR
    13. CLUSTERED BY page

    Show the query

    SELECT with EXTERN and JOIN

    1. WITH flights AS (
    2.   SELECT * FROM TABLE(
    3.   EXTERN(
    4.     '{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/flights/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2005_11.csv.zip"]}',
    5.     '{"type":"csv","findColumnsFromHeader":true}',
    6.     '[{"name":"depaturetime","type":"string"},{"name":"arrivalime","type":"string"},{"name":"Year","type":"long"},{"name":"Quarter","type":"long"},{"name":"Month","type":"long"},{"name":"DayofMonth","type":"long"},{"name":"DayOfWeek","type":"long"},{"name":"FlightDate","type":"string"},{"name":"Reporting_Airline","type":"string"},{"name":"DOT_ID_Reporting_Airline","type":"long"},{"name":"IATA_CODE_Reporting_Airline","type":"string"},{"name":"Tail_Number","type":"string"},{"name":"Flight_Number_Reporting_Airline","type":"long"},{"name":"OriginAirportID","type":"long"},{"name":"OriginAirportSeqID","type":"long"},{"name":"OriginCityMarketID","type":"long"},{"name":"Origin","type":"string"},{"name":"OriginCityName","type":"string"},{"name":"OriginState","type":"string"},{"name":"OriginStateFips","type":"long"},{"name":"OriginStateName","type":"string"},{"name":"OriginWac","type":"long"},{"name":"DestAirportID","type":"long"},{"name":"DestAirportSeqID","type":"long"},{"name":"DestCityMarketID","type":"long"},{"name":"Dest","type":"string"},{"name":"DestCityName","type":"string"},{"name":"DestState","type":"string"},{"name":"DestStateFips","type":"long"},{"name":"DestStateName","type":"string"},{"name":"DestWac","type":"long"},{"name":"CRSDepTime","type":"long"},{"name":"DepTime","type":"long"},{"name":"DepDelay","type":"long"},{"name":"DepDelayMinutes","type":"long"},{"name":"DepDel15","type":"long"},{"name":"DepartureDelayGroups","type":"long"},{"name":"DepTimeBlk","type":"string"},{"name":"TaxiOut","type":"long"},{"name":"WheelsOff","type":"long"},{"name":"WheelsOn","type":"long"},{"name":"TaxiIn","type":"long"},{"name":"CRSArrTime","type":"long"},{"name":"ArrTime","type":"long"},{"name":"ArrDelay","type":"long"},{"name":"ArrDelayMinutes","type":"long"},{"name":"ArrDel15","type":"long"},{"name":"ArrivalDelayGroups","type":"long"},{"name":"ArrTimeBlk","type":"string"},{"name":"Cancelled","type":"long"},{"name":"CancellationCode","type":"string"},{"name":"Diverted","type":"long"},{"name":"CRSElapsedTime","type":"long"},{"name":"ActualElapsedTime","type":"long"},{"name":"AirTime","type":"long"},{"name":"Flights","type":"long"},{"name":"Distance","type":"long"},{"name":"DistanceGroup","type":"long"},{"name":"CarrierDelay","type":"long"},{"name":"WeatherDelay","type":"long"},{"name":"NASDelay","type":"long"},{"name":"SecurityDelay","type":"long"},{"name":"LateAircraftDelay","type":"long"},{"name":"FirstDepTime","type":"string"},{"name":"TotalAddGTime","type":"string"},{"name":"LongestAddGTime","type":"string"},{"name":"DivAirportLandings","type":"string"},{"name":"DivReachedDest","type":"string"},{"name":"DivActualElapsedTime","type":"string"},{"name":"DivArrDelay","type":"string"},{"name":"DivDistance","type":"string"},{"name":"Div1Airport","type":"string"},{"name":"Div1AirportID","type":"string"},{"name":"Div1AirportSeqID","type":"string"},{"name":"Div1WheelsOn","type":"string"},{"name":"Div1TotalGTime","type":"string"},{"name":"Div1LongestGTime","type":"string"},{"name":"Div1WheelsOff","type":"string"},{"name":"Div1TailNum","type":"string"},{"name":"Div2Airport","type":"string"},{"name":"Div2AirportID","type":"string"},{"name":"Div2AirportSeqID","type":"string"},{"name":"Div2WheelsOn","type":"string"},{"name":"Div2TotalGTime","type":"string"},{"name":"Div2LongestGTime","type":"string"},{"name":"Div2WheelsOff","type":"string"},{"name":"Div2TailNum","type":"string"},{"name":"Div3Airport","type":"string"},{"name":"Div3AirportID","type":"string"},{"name":"Div3AirportSeqID","type":"string"},{"name":"Div3WheelsOn","type":"string"},{"name":"Div3TotalGTime","type":"string"},{"name":"Div3LongestGTime","type":"string"},{"name":"Div3WheelsOff","type":"string"},{"name":"Div3TailNum","type":"string"},{"name":"Div4Airport","type":"string"},{"name":"Div4AirportID","type":"string"},{"name":"Div4AirportSeqID","type":"string"},{"name":"Div4WheelsOn","type":"string"},{"name":"Div4TotalGTime","type":"string"},{"name":"Div4LongestGTime","type":"string"},{"name":"Div4WheelsOff","type":"string"},{"name":"Div4TailNum","type":"string"},{"name":"Div5Airport","type":"string"},{"name":"Div5AirportID","type":"string"},{"name":"Div5AirportSeqID","type":"string"},{"name":"Div5WheelsOn","type":"string"},{"name":"Div5TotalGTime","type":"string"},{"name":"Div5LongestGTime","type":"string"},{"name":"Div5WheelsOff","type":"string"},{"name":"Div5TailNum","type":"string"},{"name":"Unnamed: 109","type":"string"}]'
    7.   )
    8. )),
    9. L_AIRPORT AS (
    10.   SELECT * FROM TABLE(
    11.   EXTERN(
    12.     '{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_AIRPORT.csv"]}',
    13.     '{"type":"csv","findColumnsFromHeader":true}',
    14.     '[{"name":"Code","type":"string"},{"name":"Description","type":"string"}]'
    15.   )
    16. )),
    17. L_AIRPORT_ID AS (
    18.   SELECT * FROM TABLE(
    19.   EXTERN(
    20.     '{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_AIRPORT_ID.csv"]}',
    21.     '{"type":"csv","findColumnsFromHeader":true}',
    22.     '[{"name":"Code","type":"long"},{"name":"Description","type":"string"}]'
    23.   )
    24. )),
    25. L_AIRLINE_ID AS (
    26.   SELECT * FROM TABLE(
    27.   EXTERN(
    28.     '{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_AIRLINE_ID.csv"]}',
    29.     '{"type":"csv","findColumnsFromHeader":true}',
    30.     '[{"name":"Code","type":"long"},{"name":"Description","type":"string"}]'
    31.   )
    32. )),
    33. L_CITY_MARKET_ID AS (
    34.   SELECT * FROM TABLE(
    35.   EXTERN(
    36.     '{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_CITY_MARKET_ID.csv"]}',
    37.     '{"type":"csv","findColumnsFromHeader":true}',
    38.     '[{"name":"Code","type":"long"},{"name":"Description","type":"string"}]'
    39.   )
    40. )),
    41. L_CANCELLATION AS (
    42.   SELECT * FROM TABLE(
    43.   EXTERN(
    44.     '{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_CANCELLATION.csv"]}',
    45.     '{"type":"csv","findColumnsFromHeader":true}',
    46.     '[{"name":"Code","type":"string"},{"name":"Description","type":"string"}]'
    47.   )
    48. )),
    49. L_STATE_FIPS AS (
    50.   SELECT * FROM TABLE(
    51.   EXTERN(
    52.     '{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_STATE_FIPS.csv"]}',
    53.     '{"type":"csv","findColumnsFromHeader":true}',
    54.     '[{"name":"Code","type":"long"},{"name":"Description","type":"string"}]'
    55.   )
    56. ))
    57. SELECT
    58.   depaturetime,
    59.   arrivalime,
    60.   -- "Year",
    61.   -- Quarter,
    62.   -- "Month",
    63.   -- DayofMonth,
    64.   -- DayOfWeek,
    65.   -- FlightDate,
    66.   Reporting_Airline,
    68.   DOT_ID_Reporting_Airline,
    69.   DOTAirlineLookup.Description AS DOT_Reporting_Airline,
    71.   IATA_CODE_Reporting_Airline,
    72.   Tail_Number,
    73.   Flight_Number_Reporting_Airline,
    75.   OriginAirportID,
    76.   OriginAirportIDLookup.Description AS OriginAirport,
    79.   OriginCityMarketID,
    80.   OriginCityMarketIDLookup.Description AS OriginCityMarket,
    82.   Origin,
    83.   OriginAirportLookup.Description AS OriginDescription,
    85.   OriginCityName,
    86.   OriginState,
    88.   OriginStateFips,
    89.   OriginStateFipsLookup.Description AS OriginStateFipsDescription,
    91.   OriginStateName,
    92.   OriginWac,
    94.   DestAirportID,
    95.   DestAirportIDLookup.Description AS DestAirport,
    97.   DestAirportSeqID,
    99.   DestCityMarketID,
    100.   DestCityMarketIDLookup.Description AS DestCityMarket,
    102.   Dest,
    103.   DestAirportLookup.Description AS DestDescription,
    105.   DestCityName,
    106.   DestState,
    108.   DestStateFips,
    109.   DestStateFipsLookup.Description AS DestStateFipsDescription,
    111.   DestStateName,
    112.   DestWac,
    114.   CRSDepTime,
    115.   DepTime,
    116.   DepDelay,
    117.   DepDelayMinutes,
    118.   DepDel15,
    119.   DepartureDelayGroups,
    120.   DepTimeBlk,
    121.   TaxiOut,
    122.   WheelsOff,
    123.   WheelsOn,
    124.   TaxiIn,
    125.   CRSArrTime,
    126.   ArrTime,
    127.   ArrDelay,
    128.   ArrDelayMinutes,
    129.   ArrDel15,
    130.   ArrivalDelayGroups,
    131.   ArrTimeBlk,
    133.   Cancelled,
    134.   CancellationCode,
    135.   CancellationCodeLookup.Description AS CancellationReason,
    137.   Diverted,
    138.   CRSElapsedTime,
    139.   ActualElapsedTime,
    140.   AirTime,
    141.   Flights,
    142.   Distance,
    143.   DistanceGroup,
    144.   CarrierDelay,
    145.   WeatherDelay,
    146.   NASDelay,
    147.   SecurityDelay,
    148.   LateAircraftDelay,
    149.   FirstDepTime,
    150.   TotalAddGTime,
    151.   LongestAddGTime
    152. FROM "flights"
    153. LEFT JOIN L_AIRLINE_ID AS DOTAirlineLookup ON DOT_ID_Reporting_Airline = DOTAirlineLookup.Code
    154. LEFT JOIN L_AIRPORT AS OriginAirportLookup ON Origin = OriginAirportLookup.Code
    155. LEFT JOIN L_AIRPORT AS DestAirportLookup ON Dest = DestAirportLookup.Code
    156. LEFT JOIN L_AIRPORT_ID AS OriginAirportIDLookup ON OriginAirportID = OriginAirportIDLookup.Code
    157. LEFT JOIN L_AIRPORT_ID AS DestAirportIDLookup ON DestAirportID = DestAirportIDLookup.Code
    158. LEFT JOIN L_CITY_MARKET_ID AS OriginCityMarketIDLookup ON OriginCityMarketID = OriginCityMarketIDLookup.Code
    159. LEFT JOIN L_CITY_MARKET_ID AS DestCityMarketIDLookup ON DestCityMarketID = DestCityMarketIDLookup.Code
    160. LEFT JOIN L_STATE_FIPS AS OriginStateFipsLookup ON OriginStateFips = OriginStateFipsLookup.Code
    161. LEFT JOIN L_STATE_FIPS AS DestStateFipsLookup ON DestStateFips = DestStateFipsLookup.Code
    162. LEFT JOIN L_CANCELLATION AS CancellationCodeLookup ON CancellationCode = CancellationCodeLookup.Code
    163. LIMIT 1000