SQL-based ingestion query examples

Examples - 图1info

This page describes SQL-based batch ingestion using the druid-multi-stage-query extension, new in Druid 24.0. Refer to the ingestion methods table to determine which ingestion method is right for you.

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.

Examples - 图2tip

When you insert or replace data with SQL-based ingestion, set the context parameter finalizeAggregations to false. This context parameter is automatically set for you if you use the Druid console. If you use the API, you must explicitly set it. For more information, see Rollup.

INSERT with no rollup

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

Show the query

  1. INSERT INTO w000
  2. SELECT
  3.   TIME_PARSE("timestamp") AS __time,
  4.   isRobot,
  5.   channel,
  6.   flags,
  7.   isUnpatrolled,
  8.   page,
  9.   diffUrl,
  10.   added,
  11.   comment,
  12.   commentLength,
  13.   isNew,
  14.   isMinor,
  15.   delta,
  16.   isAnonymous,
  17.   user,
  18.   deltaBucket,
  19.   deleted,
  20.   namespace,
  21.   cityName,
  22.   countryName,
  23.   regionIsoCode,
  24.   metroCode,
  25.   countryIsoCode,
  26.   regionName
  27. FROM TABLE(
  28.     EXTERN(
  29.       '{"type":"http","uris":["https://druid.apache.org/data/wikipedia.json.gz"]}',
  30.       '{"type":"json"}',
  31.       '[{"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"}]'
  32.     )
  33.   )
  34. PARTITIONED BY HOUR
  35. CLUSTERED BY channel

INSERT with rollup

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

Show the query

  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":"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.   os,
  20.   city,
  21.   country,
  22.   forwarded_for AS ip_address,
  24.   COUNT(*) AS "cnt",
  25.   SUM(session_length) AS session_length,
  26.   APPROX_COUNT_DISTINCT_DS_HLL(event_type) AS unique_event_types
  27. FROM kttm_data
  28. WHERE os = 'iOS'
  29. GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
  30. PARTITIONED BY HOUR
  31. CLUSTERED BY browser, session

INSERT for reindexing an existing datasource

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

Show the query

  1. INSERT INTO w002
  2. SELECT
  3.   FLOOR(__time TO MINUTE) AS __time,
  4.   channel,
  5.   countryIsoCode,
  6.   countryName,
  7.   regionIsoCode,
  8.   regionName,
  9.   page,
  10.   COUNT(*) AS cnt,
  11.   SUM(added) AS sum_added,
  12.   SUM(deleted) AS sum_deleted
  13. FROM w000
  14. GROUP BY 1, 2, 3, 4, 5, 6, 7
  15. PARTITIONED BY HOUR
  16. CLUSTERED BY page

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

REPLACE an entire datasource

This example replaces the entire datasource used in the table w007 with the new query data while dropping the old data:

Show the query

  1. REPLACE INTO w007
  2. OVERWRITE ALL
  3. SELECT
  4.   TIME_PARSE("timestamp") AS __time,
  5.   isRobot,
  6.   channel,
  7.   flags,
  8.   isUnpatrolled,
  9.   page,
  10.   diffUrl,
  11.   added,
  12.   comment,
  13.   commentLength,
  14.   isNew,
  15.   isMinor,
  16.   delta,
  17.   isAnonymous,
  18.   user,
  19.   deltaBucket,
  20.   deleted,
  21.   namespace,
  22.   cityName,
  23.   countryName,
  24.   regionIsoCode,
  25.   metroCode,
  26.   countryIsoCode,
  27.   regionName
  28. FROM TABLE(
  29.     EXTERN(
  30.       '{"type":"http","uris":["https://druid.apache.org/data/wikipedia.json.gz"]}',
  31.       '{"type":"json"}',
  32.       '[{"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"}]'
  33.     )
  34.   )
  35. PARTITIONED BY HOUR
  36. CLUSTERED BY channel

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. SELECT
  4.   FLOOR(__time TO MINUTE) AS __time,
  5.   channel,
  6.   countryIsoCode,
  7.   countryName,
  8.   regionIsoCode,
  9.   regionName,
  10.   page
  11. FROM w007
  12. WHERE __time >= TIMESTAMP '2019-08-25 02:00:00' AND __time < TIMESTAMP '2019-08-25 03:00:00' AND countryName = "Canada"
  13. PARTITIONED BY HOUR
  14. CLUSTERED BY page

REPLACE for reindexing an existing datasource into itself

Show the query

  1. REPLACE INTO w000
  2. OVERWRITE ALL
  3. SELECT
  4.   FLOOR(__time TO MINUTE) AS __time,
  5.   channel,
  6.   countryIsoCode,
  7.   countryName,
  8.   regionIsoCode,
  9.   regionName,
  10.   page,
  11.   COUNT(*) AS cnt,
  12.   SUM(added) AS sum_added,
  13.   SUM(deleted) AS sum_deleted
  14. FROM w000
  15. GROUP BY 1, 2, 3, 4, 5, 6, 7
  16. PARTITIONED BY HOUR
  17. CLUSTERED BY page

SELECT with EXTERN and JOIN

Show the query

  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,
  78.   OriginAirportSeqID,
  80.   OriginCityMarketID,
  81.   OriginCityMarketIDLookup.Description AS OriginCityMarket,
  83.   Origin,
  84.   OriginAirportLookup.Description AS OriginDescription,
  86.   OriginCityName,
  87.   OriginState,
  89.   OriginStateFips,
  90.   OriginStateFipsLookup.Description AS OriginStateFipsDescription,
  92.   OriginStateName,
  93.   OriginWac,
  95.   DestAirportID,
  96.   DestAirportIDLookup.Description AS DestAirport,
  98.   DestAirportSeqID,
  100.   DestCityMarketID,
  101.   DestCityMarketIDLookup.Description AS DestCityMarket,
  103.   Dest,
  104.   DestAirportLookup.Description AS DestDescription,
  106.   DestCityName,
  107.   DestState,
  109.   DestStateFips,
  110.   DestStateFipsLookup.Description AS DestStateFipsDescription,
  112.   DestStateName,
  113.   DestWac,
  115.   CRSDepTime,
  116.   DepTime,
  117.   DepDelay,
  118.   DepDelayMinutes,
  119.   DepDel15,
  120.   DepartureDelayGroups,
  121.   DepTimeBlk,
  122.   TaxiOut,
  123.   WheelsOff,
  124.   WheelsOn,
  125.   TaxiIn,
  126.   CRSArrTime,
  127.   ArrTime,
  128.   ArrDelay,
  129.   ArrDelayMinutes,
  130.   ArrDel15,
  131.   ArrivalDelayGroups,
  132.   ArrTimeBlk,
  134.   Cancelled,
  135.   CancellationCode,
  136.   CancellationCodeLookup.Description AS CancellationReason,
  138.   Diverted,
  139.   CRSElapsedTime,
  140.   ActualElapsedTime,
  141.   AirTime,
  142.   Flights,
  143.   Distance,
  144.   DistanceGroup,
  145.   CarrierDelay,
  146.   WeatherDelay,
  147.   NASDelay,
  148.   SecurityDelay,
  149.   LateAircraftDelay,
  150.   FirstDepTime,
  151.   TotalAddGTime,
  152.   LongestAddGTime
  153. FROM "flights"
  154. LEFT JOIN L_AIRLINE_ID AS DOTAirlineLookup ON DOT_ID_Reporting_Airline = DOTAirlineLookup.Code
  155. LEFT JOIN L_AIRPORT AS OriginAirportLookup ON Origin = OriginAirportLookup.Code
  156. LEFT JOIN L_AIRPORT AS DestAirportLookup ON Dest = DestAirportLookup.Code
  157. LEFT JOIN L_AIRPORT_ID AS OriginAirportIDLookup ON OriginAirportID = OriginAirportIDLookup.Code
  158. LEFT JOIN L_AIRPORT_ID AS DestAirportIDLookup ON DestAirportID = DestAirportIDLookup.Code
  159. LEFT JOIN L_CITY_MARKET_ID AS OriginCityMarketIDLookup ON OriginCityMarketID = OriginCityMarketIDLookup.Code
  160. LEFT JOIN L_CITY_MARKET_ID AS DestCityMarketIDLookup ON DestCityMarketID = DestCityMarketIDLookup.Code
  161. LEFT JOIN L_STATE_FIPS AS OriginStateFipsLookup ON OriginStateFips = OriginStateFipsLookup.Code
  162. LEFT JOIN L_STATE_FIPS AS DestStateFipsLookup ON DestStateFips = DestStateFipsLookup.Code
  163. LEFT JOIN L_CANCELLATION AS CancellationCodeLookup ON CancellationCode = CancellationCodeLookup.Code
  164. LIMIT 1000