docs/29.0.1/multi-stage-query/examples.md - druid-website-src - Git at Google

 ---
 id: examples
 title: SQL-based ingestion query examples
 sidebar_label: Examples
 ---

 <!--
   ~ Licensed to the Apache Software Foundation (ASF) under one
   ~ or more contributor license agreements.  See the NOTICE file
   ~ distributed with this work for additional information
   ~ regarding copyright ownership.  The ASF licenses this file
   ~ to you under the Apache License, Version 2.0 (the
   ~ "License"); you may not use this file except in compliance
   ~ with the License.  You may obtain a copy of the License at
   ~
   ~   http://www.apache.org/licenses/LICENSE-2.0
   ~
   ~ Unless required by applicable law or agreed to in writing,
   ~ software distributed under the License is distributed on an
   ~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
   ~ KIND, either express or implied.  See the License for the
   ~ specific language governing permissions and limitations
   ~ under the License.
   -->

 :::info
  This page describes SQL-based batch ingestion using the [`druid-multi-stage-query`](../multi-stage-query/index.md)
  extension, new in Druid 24.0. Refer to the [ingestion methods](../ingestion/index.md#batch) 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.

 :::tip
 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](./concepts.md#rollup).
 :::

 ## INSERT with no rollup

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

 <details><summary>Show the query</summary>

 ```sql
 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
 ```

 </details>

 ## 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](./concepts.md#rollup).

 <details><summary>Show the query</summary>

 ```sql
 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
 ```
 </details>

 ## INSERT for reindexing an existing datasource

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

 <details><summary>Show the query</summary>

 ```sql
 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
 ```

 </details>

 ## INSERT with JOIN

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

 <details><summary>Show the query</summary>

 ```sql
 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
 ```

 </details>

 ## 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:

 <details><summary>Show the query</summary>

 ```sql
 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
 ```

 </details>

 ## REPLACE for replacing a specific time segment

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

 <details><summary>Show the query</summary>

 ```sql
 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
 ```

 </details>

 ## REPLACE for reindexing an existing datasource into itself

 <details><summary>Show the query</summary>

 ```sql
 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
 ```

 </details>

 ## SELECT with EXTERN and JOIN

 <details><summary>Show the query</summary>

 ```sql
 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
 ```

 </details>
	---
	id: examples
	title: SQL-based ingestion query examples
	sidebar_label: Examples
	---

	<!--
	~ Licensed to the Apache Software Foundation (ASF) under one
	~ or more contributor license agreements. See the NOTICE file
	~ distributed with this work for additional information
	~ regarding copyright ownership. The ASF licenses this file
	~ to you under the Apache License, Version 2.0 (the
	~ "License"); you may not use this file except in compliance
	~ with the License. You may obtain a copy of the License at
	~
	~ http://www.apache.org/licenses/LICENSE-2.0
	~
	~ Unless required by applicable law or agreed to in writing,
	~ software distributed under the License is distributed on an
	~ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	~ KIND, either express or implied. See the License for the
	~ specific language governing permissions and limitations
	~ under the License.
	-->

	:::info
	This page describes SQL-based batch ingestion using the [`druid-multi-stage-query`](../multi-stage-query/index.md)
	extension, new in Druid 24.0. Refer to the [ingestion methods](../ingestion/index.md#batch) 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.

	:::tip
	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](./concepts.md#rollup).
	:::

	## INSERT with no rollup

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

	<details><summary>Show the query</summary>

	```sql
	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
	```

	</details>

	## 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](./concepts.md#rollup).

	<details><summary>Show the query</summary>

	```sql
	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
	```
	</details>

	## INSERT for reindexing an existing datasource

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

	<details><summary>Show the query</summary>

	```sql
	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
	```

	</details>

	## INSERT with JOIN

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

	<details><summary>Show the query</summary>

	```sql
	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
	```

	</details>

	## 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:

	<details><summary>Show the query</summary>

	```sql
	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
	```

	</details>

	## REPLACE for replacing a specific time segment

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

	<details><summary>Show the query</summary>

	```sql
	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
	```

	</details>

	## REPLACE for reindexing an existing datasource into itself

	<details><summary>Show the query</summary>

	```sql
	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
	```

	</details>

	## SELECT with EXTERN and JOIN

	<details><summary>Show the query</summary>

	```sql
	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
	```

	</details>