:::info 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.
This example inserts data into a table named w000 without performing any data rollup:
INSERT INTO w000 SELECT TIME_PARSE("timestamp") AS __time, isRobot, channel, flags, isUnpatrolled, page, diffUrl, added, comment, commentLength, isNew, isMinor, delta, isAnonymous, user, deltaBucket, deleted, namespace, cityName, countryName, regionIsoCode, metroCode, countryIsoCode, regionName FROM TABLE( EXTERN( '{"type":"http","uris":["https://druid.apache.org/data/wikipedia.json.gz"]}', '{"type":"json"}', '[{"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"}]' ) ) PARTITIONED BY HOUR CLUSTERED BY channel
This example inserts data into a table named kttm_rollup and performs data rollup. This example implements the recommendations described in Rollup.
INSERT INTO "kttm_rollup" WITH kttm_data AS ( SELECT * FROM TABLE( EXTERN( '{"type":"http","uris":["https://static.imply.io/example-data/kttm-v2/kttm-v2-2019-08-25.json.gz"]}', '{"type":"json"}', '[{"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"}]' ) )) SELECT FLOOR(TIME_PARSE("timestamp") TO MINUTE) AS __time, session, agent_category, agent_type, browser, browser_version os, city, country, forwarded_for AS ip_address, COUNT(*) AS "cnt", SUM(session_length) AS session_length, APPROX_COUNT_DISTINCT_DS_HLL(event_type) AS unique_event_types FROM kttm_data WHERE os = 'iOS' GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 PARTITIONED BY HOUR CLUSTERED BY browser, session
This example aggregates data from a table named w000 and inserts the result into w002.
INSERT INTO w002 SELECT FLOOR(__time TO MINUTE) AS __time, channel, countryIsoCode, countryName, regionIsoCode, regionName, page, COUNT(*) AS cnt, SUM(added) AS sum_added, SUM(deleted) AS sum_deleted FROM w000 GROUP BY 1, 2, 3, 4, 5, 6, 7 PARTITIONED BY HOUR CLUSTERED BY page
This example inserts data into a table named w003 and joins data from two sources:
INSERT INTO w003 WITH wikidata AS (SELECT * FROM TABLE( EXTERN( '{"type":"http","uris":["https://druid.apache.org/data/wikipedia.json.gz"]}', '{"type":"json"}', '[{"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"}]' ) )), countries AS (SELECT * FROM TABLE( EXTERN( '{"type":"http","uris":["https://static.imply.io/example-data/lookup/countries.tsv"]}', '{"type":"tsv","findColumnsFromHeader":true}', '[{"name":"Country","type":"string"},{"name":"Capital","type":"string"},{"name":"ISO3","type":"string"},{"name":"ISO2","type":"string"}]' ) )) SELECT TIME_PARSE("timestamp") AS __time, isRobot, channel, flags, isUnpatrolled, page, diffUrl, added, comment, commentLength, isNew, isMinor, delta, isAnonymous, user, deltaBucket, deleted, namespace, cityName, countryName, regionIsoCode, metroCode, countryIsoCode, countries.Capital AS countryCapital, regionName FROM wikidata LEFT JOIN countries ON wikidata.countryIsoCode = countries.ISO2 PARTITIONED BY HOUR
This example replaces the entire datasource used in the table w007 with the new query data while dropping the old data:
REPLACE INTO w007 OVERWRITE ALL SELECT TIME_PARSE("timestamp") AS __time, isRobot, channel, flags, isUnpatrolled, page, diffUrl, added, comment, commentLength, isNew, isMinor, delta, isAnonymous, user, deltaBucket, deleted, namespace, cityName, countryName, regionIsoCode, metroCode, countryIsoCode, regionName FROM TABLE( EXTERN( '{"type":"http","uris":["https://druid.apache.org/data/wikipedia.json.gz"]}', '{"type":"json"}', '[{"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"}]' ) ) PARTITIONED BY HOUR CLUSTERED BY channel
This example replaces certain segments in a datasource with the new query data while dropping old segments:
REPLACE INTO w007 OVERWRITE WHERE __time >= TIMESTAMP '2019-08-25 02:00:00' AND __time < TIMESTAMP '2019-08-25 03:00:00' SELECT FLOOR(__time TO MINUTE) AS __time, channel, countryIsoCode, countryName, regionIsoCode, regionName, page FROM w007 WHERE __time >= TIMESTAMP '2019-08-25 02:00:00' AND __time < TIMESTAMP '2019-08-25 03:00:00' AND countryName = "Canada" PARTITIONED BY HOUR CLUSTERED BY page
REPLACE INTO w000 OVERWRITE ALL SELECT FLOOR(__time TO MINUTE) AS __time, channel, countryIsoCode, countryName, regionIsoCode, regionName, page, COUNT(*) AS cnt, SUM(added) AS sum_added, SUM(deleted) AS sum_deleted FROM w000 GROUP BY 1, 2, 3, 4, 5, 6, 7 PARTITIONED BY HOUR CLUSTERED BY page
WITH flights AS (
SELECT * FROM TABLE(
EXTERN(
'{"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"]}',
'{"type":"csv","findColumnsFromHeader":true}',
'[{"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"}]'
)
)),
L_AIRPORT AS (
SELECT * FROM TABLE(
EXTERN(
'{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_AIRPORT.csv"]}',
'{"type":"csv","findColumnsFromHeader":true}',
'[{"name":"Code","type":"string"},{"name":"Description","type":"string"}]'
)
)),
L_AIRPORT_ID AS (
SELECT * FROM TABLE(
EXTERN(
'{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_AIRPORT_ID.csv"]}',
'{"type":"csv","findColumnsFromHeader":true}',
'[{"name":"Code","type":"long"},{"name":"Description","type":"string"}]'
)
)),
L_AIRLINE_ID AS (
SELECT * FROM TABLE(
EXTERN(
'{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_AIRLINE_ID.csv"]}',
'{"type":"csv","findColumnsFromHeader":true}',
'[{"name":"Code","type":"long"},{"name":"Description","type":"string"}]'
)
)),
L_CITY_MARKET_ID AS (
SELECT * FROM TABLE(
EXTERN(
'{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_CITY_MARKET_ID.csv"]}',
'{"type":"csv","findColumnsFromHeader":true}',
'[{"name":"Code","type":"long"},{"name":"Description","type":"string"}]'
)
)),
L_CANCELLATION AS (
SELECT * FROM TABLE(
EXTERN(
'{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_CANCELLATION.csv"]}',
'{"type":"csv","findColumnsFromHeader":true}',
'[{"name":"Code","type":"string"},{"name":"Description","type":"string"}]'
)
)),
L_STATE_FIPS AS (
SELECT * FROM TABLE(
EXTERN(
'{"type":"http","uris":["https://static.imply.io/example-data/flight_on_time/dimensions/L_STATE_FIPS.csv"]}',
'{"type":"csv","findColumnsFromHeader":true}',
'[{"name":"Code","type":"long"},{"name":"Description","type":"string"}]'
)
))
SELECT
depaturetime,
arrivalime,
-- "Year",
-- Quarter,
-- "Month",
-- DayofMonth,
-- DayOfWeek,
-- FlightDate,
Reporting_Airline,
DOT_ID_Reporting_Airline,
DOTAirlineLookup.Description AS DOT_Reporting_Airline,
IATA_CODE_Reporting_Airline,
Tail_Number,
Flight_Number_Reporting_Airline,
OriginAirportID,
OriginAirportIDLookup.Description AS OriginAirport,
OriginAirportSeqID,
OriginCityMarketID,
OriginCityMarketIDLookup.Description AS OriginCityMarket,
Origin,
OriginAirportLookup.Description AS OriginDescription,
OriginCityName,
OriginState,
OriginStateFips,
OriginStateFipsLookup.Description AS OriginStateFipsDescription,
OriginStateName,
OriginWac,
DestAirportID,
DestAirportIDLookup.Description AS DestAirport,
DestAirportSeqID,
DestCityMarketID,
DestCityMarketIDLookup.Description AS DestCityMarket,
Dest,
DestAirportLookup.Description AS DestDescription,
DestCityName,
DestState,
DestStateFips,
DestStateFipsLookup.Description AS DestStateFipsDescription,
DestStateName,
DestWac,
CRSDepTime,
DepTime,
DepDelay,
DepDelayMinutes,
DepDel15,
DepartureDelayGroups,
DepTimeBlk,
TaxiOut,
WheelsOff,
WheelsOn,
TaxiIn,
CRSArrTime,
ArrTime,
ArrDelay,
ArrDelayMinutes,
ArrDel15,
ArrivalDelayGroups,
ArrTimeBlk,
Cancelled,
CancellationCode,
CancellationCodeLookup.Description AS CancellationReason,
Diverted,
CRSElapsedTime,
ActualElapsedTime,
AirTime,
Flights,
Distance,
DistanceGroup,
CarrierDelay,
WeatherDelay,
NASDelay,
SecurityDelay,
LateAircraftDelay,
FirstDepTime,
TotalAddGTime,
LongestAddGTime
FROM "flights"
LEFT JOIN L_AIRLINE_ID AS DOTAirlineLookup ON DOT_ID_Reporting_Airline = DOTAirlineLookup.Code
LEFT JOIN L_AIRPORT AS OriginAirportLookup ON Origin = OriginAirportLookup.Code
LEFT JOIN L_AIRPORT AS DestAirportLookup ON Dest = DestAirportLookup.Code
LEFT JOIN L_AIRPORT_ID AS OriginAirportIDLookup ON OriginAirportID = OriginAirportIDLookup.Code
LEFT JOIN L_AIRPORT_ID AS DestAirportIDLookup ON DestAirportID = DestAirportIDLookup.Code
LEFT JOIN L_CITY_MARKET_ID AS OriginCityMarketIDLookup ON OriginCityMarketID = OriginCityMarketIDLookup.Code
LEFT JOIN L_CITY_MARKET_ID AS DestCityMarketIDLookup ON DestCityMarketID = DestCityMarketIDLookup.Code
LEFT JOIN L_STATE_FIPS AS OriginStateFipsLookup ON OriginStateFips = OriginStateFipsLookup.Code
LEFT JOIN L_STATE_FIPS AS DestStateFipsLookup ON DestStateFips = DestStateFipsLookup.Code
LEFT JOIN L_CANCELLATION AS CancellationCodeLookup ON CancellationCode = CancellationCodeLookup.Code
LIMIT 1000