vtlim commented on code in PR #14609: URL: https://github.com/apache/druid/pull/14609#discussion_r1279956788
########## docs/operations/durable-storage.md: ########## @@ -29,6 +29,10 @@ You can use durable storage to improve querying from deep storage and SQL-based Durable storage for queries from deep storage provides a location where you can write the results of deep storage queries to. Durable storage for SQL-based ingestion is used to temporarily house intermediate files, which can improve reliability. +Enabling durable storage also enables the use of local disk to store temporary files, such as the intermediate files produced +by the super sorter. Tasks will use whatever has been configured for their temporary usage as described in [Configuring task storage sizes](../ingestion/tasks.md#configuring-task-storage-sizes). Review Comment: What's the super sorter? Link to related docs? ########## docs/querying/query-from-deep-storage.md: ########## @@ -0,0 +1,193 @@ +--- +id: query-deep-storage +title: "Query from deep storage" +--- + +<!-- + ~ 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. + --> + +> Query from deep storage is an [experimental feature](../development/experimental.md). + +Druid can query segments that are only stored in deep storage. Running a query from deep storage is slower than running queries from segments that are loaded on Historical processes, but it's a great tool for data that you either access infrequently or where the low latency results that typical Druid queries provide is not necessary. Queries from deep storage can increase the surface area of data available to query without requiring you to scale your Historical processes to accommodate more segments. + +## Keep segments in deep storage only + +Any data you ingest into Druid is already stored in deep storage, so you don't need to perform any additional configuration from that perspective. However, to take advantage of the cost savings that querying from deep storage provides, make sure not all your segments get loaded onto Historical processes. + +To do this, configure [load rules](../operations/rule-configuration.md#load-rules) to load only the segments you do want on Historical processes. + +The easiest way to do this is to set `tieredReplicants` to an empty array and `useDefaultTierForNull` to `false`: + +```json +[ + { + "interval": "2016-06-27T00:00:00.000Z/2016-06-27T02:59:00.000Z", + "tieredReplicants": {}, + "useDefaultTierForNull": false, + "type": "loadByInterval" + } +] +``` + +To configure the load rules through the Druid console, go to **Datasources > ... in the Actions column > Edit retention rules**. Then, paste the provided JSON into the JSON tab: + + + + +You can verify that a segment is not loaded on any Historical tiers by querying the Druid metadata table: + +```sql +SELECT "segment_id", "replication_factor" FROM sys."segments" WHERE "replication_factor" = 0 AND "datasource" = YOUR_DATASOURCE +``` + +Segments with a `replication_factor` of `0` are not assigned to any Historical tiers. Queries against these segments are run directly against the segment in deep storage. + +You can also confirm this through the Druid console. On the **Segments** page, see the **Replication factor** column. + +Keep the following in mind when working with load rules to control what exists only in deep storage: + +- At least one of the segments in a datasource must be loaded onto a Historical process so that Druid can plan the query. The segment on the Historical process can be any segment from the datasource. It does not need to be a specific segment. One way to verify that a datasource has at least one segment on a Historical process is if it's visible in the Druid console. +- The actual number of replicas may differ from the replication factor temporarily as Druid processes your load rules. + +## Run a query from deep storage + +### Submit a query + +You can query data from deep storage by submitting a query to the API using `POST /sql/statements` or the Druid console. Druid uses the multi-stage query (MSQ) task engine to perform the query. + +To run a query from deep storage, send your query to the Router using the POST method: + +``` +POST https://ROUTER:8888/druid/v2/sql/statements +``` + +Submitting a query from deep storage uses the same syntax as any other Druid SQL query where the query is contained in the "query" field in the JSON object within the request payload. For example: + +```json +{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} +``` + +Generally, the request body fields are the same between the `sql` and `/sql/statements` endpoints. Review Comment: ```suggestion Generally, the request body fields are the same between the `sql` and `sql/statements` endpoints. ``` ########## docs/querying/query-from-deep-storage.md: ########## @@ -0,0 +1,193 @@ +--- +id: query-deep-storage +title: "Query from deep storage" +--- + +<!-- + ~ 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. + --> + +> Query from deep storage is an [experimental feature](../development/experimental.md). + +Druid can query segments that are only stored in deep storage. Running a query from deep storage is slower than running queries from segments that are loaded on Historical processes, but it's a great tool for data that you either access infrequently or where the low latency results that typical Druid queries provide is not necessary. Queries from deep storage can increase the surface area of data available to query without requiring you to scale your Historical processes to accommodate more segments. + +## Keep segments in deep storage only + +Any data you ingest into Druid is already stored in deep storage, so you don't need to perform any additional configuration from that perspective. However, to take advantage of the cost savings that querying from deep storage provides, make sure not all your segments get loaded onto Historical processes. + +To do this, configure [load rules](../operations/rule-configuration.md#load-rules) to load only the segments you do want on Historical processes. + +The easiest way to do this is to set `tieredReplicants` to an empty array and `useDefaultTierForNull` to `false`: + +```json +[ + { + "interval": "2016-06-27T00:00:00.000Z/2016-06-27T02:59:00.000Z", + "tieredReplicants": {}, + "useDefaultTierForNull": false, + "type": "loadByInterval" + } +] +``` + +To configure the load rules through the Druid console, go to **Datasources > ... in the Actions column > Edit retention rules**. Then, paste the provided JSON into the JSON tab: + + + + +You can verify that a segment is not loaded on any Historical tiers by querying the Druid metadata table: + +```sql +SELECT "segment_id", "replication_factor" FROM sys."segments" WHERE "replication_factor" = 0 AND "datasource" = YOUR_DATASOURCE +``` + +Segments with a `replication_factor` of `0` are not assigned to any Historical tiers. Queries against these segments are run directly against the segment in deep storage. + +You can also confirm this through the Druid console. On the **Segments** page, see the **Replication factor** column. + +Keep the following in mind when working with load rules to control what exists only in deep storage: + +- At least one of the segments in a datasource must be loaded onto a Historical process so that Druid can plan the query. The segment on the Historical process can be any segment from the datasource. It does not need to be a specific segment. One way to verify that a datasource has at least one segment on a Historical process is if it's visible in the Druid console. +- The actual number of replicas may differ from the replication factor temporarily as Druid processes your load rules. + +## Run a query from deep storage + +### Submit a query + +You can query data from deep storage by submitting a query to the API using `POST /sql/statements` or the Druid console. Druid uses the multi-stage query (MSQ) task engine to perform the query. + +To run a query from deep storage, send your query to the Router using the POST method: + +``` +POST https://ROUTER:8888/druid/v2/sql/statements +``` + +Submitting a query from deep storage uses the same syntax as any other Druid SQL query where the query is contained in the "query" field in the JSON object within the request payload. For example: + +```json +{"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar'"} +``` + +Generally, the request body fields are the same between the `sql` and `/sql/statements` endpoints. + +There are additional context parameters for `/sql/statements` specifically: Review Comment: ```suggestion There are additional context parameters for `sql/statements` specifically: ``` ########## docs/operations/rule-configuration.md: ########## @@ -120,6 +120,8 @@ All load rules can have these properties: Specific types of load rules discussed below may have other properties too. +Load rules are also how you take advantage of the resource savings that [query the data from deep storage](../querying/query-from-deep-storage.md) provides. One way to configure data so that certain segments are not loaded onto Historical tiers but is available to query from deep storage is to set `tieredReplicants` to an empty array and `useDefaultTierForNull` to `false` for those segments, either by interval or by period. Review Comment: ```suggestion Load rules are also how you take advantage of the resource savings that [query the data from deep storage](../querying/query-from-deep-storage.md) provides. One way to configure data so that certain segments are not loaded onto Historical tiers but are available to query from deep storage is to set `tieredReplicants` to an empty array and `useDefaultTierForNull` to `false` for those segments, either by interval or by period. ``` ########## docs/operations/durable-storage.md: ########## @@ -0,0 +1,84 @@ +--- +id: durable-storage +title: "Durable storage for the multi-stage query engine" +sidebar_label: "Durable storage" +--- + +<!-- + ~ 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. + --> + +You can use durable storage to improve querying from deep storage and SQL-based ingestion. + +> Note that only S3 is supported as a durable storage location. + +Durable storage for queries from deep storage provides a location where you can write the results of deep storage queries to. Durable storage for SQL-based ingestion is used to temporarily house intermediate files, which can improve reliability. + +Enabling durable storage also enables the use of local disk to store temporary files, such as the intermediate files produced +by the super sorter. Tasks will use whatever has been configured for their temporary usage as described in [Configuring task storage sizes](../ingestion/tasks.md#configuring-task-storage-sizes). +If the configured limit is too low, Druid may throw the error, `NotEnoughTemporaryStorageFault`. + +## Enable durable storage + +To enable durable storage, you need to set the following common service properties: + +``` +druid.msq.intermediate.storage.enable=true +druid.msq.intermediate.storage.type=s3 +druid.msq.intermediate.storage.bucket=YOUR_BUCKET +druid.msq.intermediate.storage.prefix=YOUR_PREFIX +druid.msq.intermediate.storage.tempDir=/path/to/your/temp/dir +``` + +For detailed information about the settings related to durable storage, see [Durable storage configurations](../multi-stage-query/reference.md#durable-storage-configurations). + + +## Use durable storage for SQL-based ingestion queries + +When you run a query, include the context parameter `durableShuffleStorage` and set it to `true`. + +For queries where you want to use fault tolerance for workers, set `faultTolerance` to `true`, which automatically sets `durableShuffleStorage` to `true`. + +## Use durable storage for queries from deep storage + +Saving the final results for queries from deep storage to durable storage instead of task reports allows you to fetch large result sets. + +When you run a query, include the context parameter `selectDestination` and set it to `DURABLESTORAGE`: + +```json + "context":{ + ... + "selectDestination": "DURABLESTORAGE" + } +``` + +You can also write intermediate results to durable storage (`durableShuffleStorage`) for better reliability. The location where workers write intermediate results is different than the location where final results get stored. This means that durable storage for results can be enabled even if you don't write intermediate results to durable storage. + +If you write the results for queries from deep storage to durable storage, the results are cleaned up when the task is removed from the metadata store. + +## Durable storage clean up + +To prevent durable storage from getting filled up with temporary files in case the tasks fail to clean them up, a periodic +cleaner can be scheduled to clean the directories corresponding to which there isn't a controller task running. It utilizes +the storage connector to work upon the durable storage. The durable storage location should only be utilized to store the output +for the cluster's MSQ tasks. If the location contains other files or directories, then they will get cleaned up as well. + +Use `druid.msq.intermediate.storage.cleaner.enabled` and `druid.msq.intermediate.storage.cleaner.delaySEconds` to configure the cleaner. For more information, see [Durable storage configurations](../multi-stage-query/reference.md#durable-storage-configurations) Review Comment: ```suggestion Use `druid.msq.intermediate.storage.cleaner.enabled` and `druid.msq.intermediate.storage.cleaner.delaySEconds` to configure the cleaner. For more information, see [Durable storage configurations](../multi-stage-query/reference.md#durable-storage-configurations). ``` ########## docs/operations/durable-storage.md: ########## @@ -0,0 +1,84 @@ +--- +id: durable-storage +title: "Durable storage for the multi-stage query engine" +sidebar_label: "Durable storage" +--- + +<!-- + ~ 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. + --> + +You can use durable storage to improve querying from deep storage and SQL-based ingestion. + +> Note that only S3 is supported as a durable storage location. + +Durable storage for queries from deep storage provides a location where you can write the results of deep storage queries to. Durable storage for SQL-based ingestion is used to temporarily house intermediate files, which can improve reliability. + +Enabling durable storage also enables the use of local disk to store temporary files, such as the intermediate files produced +by the super sorter. Tasks will use whatever has been configured for their temporary usage as described in [Configuring task storage sizes](../ingestion/tasks.md#configuring-task-storage-sizes). +If the configured limit is too low, Druid may throw the error, `NotEnoughTemporaryStorageFault`. + +## Enable durable storage + +To enable durable storage, you need to set the following common service properties: + +``` +druid.msq.intermediate.storage.enable=true +druid.msq.intermediate.storage.type=s3 +druid.msq.intermediate.storage.bucket=YOUR_BUCKET +druid.msq.intermediate.storage.prefix=YOUR_PREFIX +druid.msq.intermediate.storage.tempDir=/path/to/your/temp/dir +``` + +For detailed information about the settings related to durable storage, see [Durable storage configurations](../multi-stage-query/reference.md#durable-storage-configurations). + + +## Use durable storage for SQL-based ingestion queries + +When you run a query, include the context parameter `durableShuffleStorage` and set it to `true`. + +For queries where you want to use fault tolerance for workers, set `faultTolerance` to `true`, which automatically sets `durableShuffleStorage` to `true`. + +## Use durable storage for queries from deep storage + +Saving the final results for queries from deep storage to durable storage instead of task reports allows you to fetch large result sets. Review Comment: Do you first want to say that query results from deep storage are stored in task reports? I didn't see where that was mentioned but it seems one should know that before getting to this section. ########## docs/tutorials/tutorial-query-deep-storage.md: ########## @@ -0,0 +1,293 @@ +--- +id: tutorial-query-deep-storage +title: "Tutorial: Query from deep storage" +sidebar_label: "Query from deep storage" +--- + +<!-- + ~ 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. + --> + + +> Query from deep storage is an [experimental feature](../development/experimental.md). + +Query from deep storage allows you to query segments that are stored only in deep storage, which provides lower costs than if you were to load everything onto Historical processes. The tradeoff is that queries from deep storage may take longer to complete. + +This tutorial walks you through loading example data, configuring load rules so that not all the segments get loaded onto Historical processes, and querying data from deep storage. + +To run the queries in this tutorial, replace `ROUTER:PORT` with the location of the Router process and its port number. For example, use `localhost:8888` for the quickstart deployment. + +For more general information, see [Query from deep storage](../querying/query-from-deep-storage.md). + +## Load example data + +Use the **Load data** wizard or the following SQL query to ingest the `wikipedia` sample datasource bundled with Druid. If you use the wizard, make sure you change the partitioning to be by hour. + +Partitioning by hour provides more segment granularity, so you can selectively load segments onto Historicals or keep them in deep storage. + +<details><summary>Show the query</summary> + +```sql +REPLACE INTO "wikipedia" OVERWRITE ALL +WITH "ext" AS (SELECT * +FROM TABLE( + EXTERN( + '{"type":"http","uris":["https://druid.apache.org/data/wikipedia.json.gz"]}', + '{"type":"json"}' + ) +) EXTEND ("isRobot" VARCHAR, "channel" VARCHAR, "timestamp" VARCHAR, "flags" VARCHAR, "isUnpatrolled" VARCHAR, "page" VARCHAR, "diffUrl" VARCHAR, "added" BIGINT, "comment" VARCHAR, "commentLength" BIGINT, "isNew" VARCHAR, "isMinor" VARCHAR, "delta" BIGINT, "isAnonymous" VARCHAR, "user" VARCHAR, "deltaBucket" BIGINT, "deleted" BIGINT, "namespace" VARCHAR, "cityName" VARCHAR, "countryName" VARCHAR, "regionIsoCode" VARCHAR, "metroCode" BIGINT, "countryIsoCode" VARCHAR, "regionName" VARCHAR)) +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 "ext" +PARTITIONED BY HOUR +``` + +</details> + +## Configure a load rule + +The load rule configures Druid to keep any segments that fall within the following interval only in deep storage: + +``` +2016-06-27T00:00:00.000Z/2016-06-27T02:59:00.000Z +``` + +The JSON form of the rule is as follows: + +```json +[ + { + "interval": "2016-06-27T00:00:00.000Z/2016-06-27T02:59:00.000Z", + "tieredReplicants": {}, + "useDefaultTierForNull": false, + "type": "loadByInterval" + } +] +``` + +The rest of the segments use the default load rules for the cluster. For the quickstart, that means all the other segments get loaded onto Historical processes. + +You can configure the load rules through the API or the Druid console. To configure the load rules through the Druid console, go to **Datasources > ... in the Actions column > Edit retention rules**. Then, paste the provided JSON into the JSON tab: + + + + +### Verify the replication factor + +Segments that are only available from deep storage have a `replication_factor` of 0 in the Druid system table. You can verify that your load rule worked as intended using the following query: + +```sql +SELECT "segment_id", "replication_factor", "num_replicas" FROM sys."segments" WHERE datasource = 'wikipedia' +``` + +You can also verify it through the Druid console by checking the **Replication factor** column in the **Segments** view. + +Note that the number of replicas and replication factor may differ temporarily as Druid processes your retention rules. + +## Query from deep storage + +Now that there are segments that are only available from deep storage, run the following query: + +```sql +SELECT page FROM wikipedia WHERE __time < TIMESTAMP'2016-06-27 00:10:00' LIMIT 10 +``` + +With the context parameter: + +```json +"executionMode": "ASYNC" +``` + +For example, run the following curl command: + +``` +curl --location 'http://localhost:8888/druid/v2/sql/statements' \ +--header 'Content-Type: application/json' \ +--data '{ + "query":"SELECT page FROM wikipedia WHERE __time < TIMESTAMP'\''2016-06-27 00:10:00'\'' LIMIT 10", + "context":{ + "executionMode":"ASYNC" + } +}' +``` + +This query looks for records with timestamps that precede `00:10:00`. Based on the load rule you configured earlier, this data is only available from deep storage. + +When you submit the query from deep storage through the API, you get the following response: + +<details><summary>Show the response</summary> + +```json +{ + "queryId": "query-6888b6f6-e597-456c-9004-222b05b97051", + "state": "ACCEPTED", + "createdAt": "2023-07-28T21:59:02.334Z", + "schema": [ + { + "name": "page", + "type": "VARCHAR", + "nativeType": "STRING" + } + ], + "durationMs": -1 +} +``` + +Make sure you note the `queryID`. You'll need it to interact with the query. + +</details> + +Compare this to if you were to submit the query to Druid SQL's regular endpoint, `POST /sq/`: Review Comment: ```suggestion Compare this to if you were to submit the query to Druid SQL's regular endpoint, `POST /sql`: ``` ########## docs/tutorials/tutorial-query-deep-storage.md: ########## @@ -0,0 +1,293 @@ +--- +id: tutorial-query-deep-storage +title: "Tutorial: Query from deep storage" +sidebar_label: "Query from deep storage" +--- + +<!-- + ~ 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. + --> + + +> Query from deep storage is an [experimental feature](../development/experimental.md). + +Query from deep storage allows you to query segments that are stored only in deep storage, which provides lower costs than if you were to load everything onto Historical processes. The tradeoff is that queries from deep storage may take longer to complete. + +This tutorial walks you through loading example data, configuring load rules so that not all the segments get loaded onto Historical processes, and querying data from deep storage. + +To run the queries in this tutorial, replace `ROUTER:PORT` with the location of the Router process and its port number. For example, use `localhost:8888` for the quickstart deployment. + +For more general information, see [Query from deep storage](../querying/query-from-deep-storage.md). + +## Load example data + +Use the **Load data** wizard or the following SQL query to ingest the `wikipedia` sample datasource bundled with Druid. If you use the wizard, make sure you change the partitioning to be by hour. + +Partitioning by hour provides more segment granularity, so you can selectively load segments onto Historicals or keep them in deep storage. + +<details><summary>Show the query</summary> + +```sql +REPLACE INTO "wikipedia" OVERWRITE ALL +WITH "ext" AS (SELECT * +FROM TABLE( + EXTERN( + '{"type":"http","uris":["https://druid.apache.org/data/wikipedia.json.gz"]}', + '{"type":"json"}' + ) +) EXTEND ("isRobot" VARCHAR, "channel" VARCHAR, "timestamp" VARCHAR, "flags" VARCHAR, "isUnpatrolled" VARCHAR, "page" VARCHAR, "diffUrl" VARCHAR, "added" BIGINT, "comment" VARCHAR, "commentLength" BIGINT, "isNew" VARCHAR, "isMinor" VARCHAR, "delta" BIGINT, "isAnonymous" VARCHAR, "user" VARCHAR, "deltaBucket" BIGINT, "deleted" BIGINT, "namespace" VARCHAR, "cityName" VARCHAR, "countryName" VARCHAR, "regionIsoCode" VARCHAR, "metroCode" BIGINT, "countryIsoCode" VARCHAR, "regionName" VARCHAR)) +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 "ext" +PARTITIONED BY HOUR +``` + +</details> + +## Configure a load rule + +The load rule configures Druid to keep any segments that fall within the following interval only in deep storage: + +``` +2016-06-27T00:00:00.000Z/2016-06-27T02:59:00.000Z +``` + +The JSON form of the rule is as follows: + +```json +[ + { + "interval": "2016-06-27T00:00:00.000Z/2016-06-27T02:59:00.000Z", + "tieredReplicants": {}, + "useDefaultTierForNull": false, + "type": "loadByInterval" + } +] +``` + +The rest of the segments use the default load rules for the cluster. For the quickstart, that means all the other segments get loaded onto Historical processes. + +You can configure the load rules through the API or the Druid console. To configure the load rules through the Druid console, go to **Datasources > ... in the Actions column > Edit retention rules**. Then, paste the provided JSON into the JSON tab: + + + + +### Verify the replication factor + +Segments that are only available from deep storage have a `replication_factor` of 0 in the Druid system table. You can verify that your load rule worked as intended using the following query: + +```sql +SELECT "segment_id", "replication_factor", "num_replicas" FROM sys."segments" WHERE datasource = 'wikipedia' +``` + +You can also verify it through the Druid console by checking the **Replication factor** column in the **Segments** view. + +Note that the number of replicas and replication factor may differ temporarily as Druid processes your retention rules. + +## Query from deep storage + +Now that there are segments that are only available from deep storage, run the following query: + +```sql +SELECT page FROM wikipedia WHERE __time < TIMESTAMP'2016-06-27 00:10:00' LIMIT 10 +``` + +With the context parameter: + +```json +"executionMode": "ASYNC" +``` + +For example, run the following curl command: + +``` +curl --location 'http://localhost:8888/druid/v2/sql/statements' \ +--header 'Content-Type: application/json' \ +--data '{ + "query":"SELECT page FROM wikipedia WHERE __time < TIMESTAMP'\''2016-06-27 00:10:00'\'' LIMIT 10", + "context":{ + "executionMode":"ASYNC" + } +}' +``` + +This query looks for records with timestamps that precede `00:10:00`. Based on the load rule you configured earlier, this data is only available from deep storage. + +When you submit the query from deep storage through the API, you get the following response: + +<details><summary>Show the response</summary> + +```json +{ + "queryId": "query-6888b6f6-e597-456c-9004-222b05b97051", + "state": "ACCEPTED", + "createdAt": "2023-07-28T21:59:02.334Z", + "schema": [ + { + "name": "page", + "type": "VARCHAR", + "nativeType": "STRING" + } + ], + "durationMs": -1 +} +``` + +Make sure you note the `queryID`. You'll need it to interact with the query. + +</details> + +Compare this to if you were to submit the query to Druid SQL's regular endpoint, `POST /sq/`: + +``` +curl --location 'http://localhost:8888/druid/v2/sql/statements' \ Review Comment: This isn't the regular endpoint is it? ########## docs/querying/query-from-deep-storage.md: ########## @@ -0,0 +1,193 @@ +--- +id: query-deep-storage +title: "Query from deep storage" +--- + +<!-- + ~ 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. + --> + +> Query from deep storage is an [experimental feature](../development/experimental.md). + +Druid can query segments that are only stored in deep storage. Running a query from deep storage is slower than running queries from segments that are loaded on Historical processes, but it's a great tool for data that you either access infrequently or where the low latency results that typical Druid queries provide is not necessary. Queries from deep storage can increase the surface area of data available to query without requiring you to scale your Historical processes to accommodate more segments. + +## Keep segments in deep storage only + +Any data you ingest into Druid is already stored in deep storage, so you don't need to perform any additional configuration from that perspective. However, to take advantage of the cost savings that querying from deep storage provides, make sure not all your segments get loaded onto Historical processes. + +To do this, configure [load rules](../operations/rule-configuration.md#load-rules) to load only the segments you do want on Historical processes. + +The easiest way to do this is to set `tieredReplicants` to an empty array and `useDefaultTierForNull` to `false`: Review Comment: Should also add description of what the interval is for -- according to the tutorial, data that falls within the interval range will live only on deep storage, but that seems contradicting to the previous line >To do this, configure [load rules](../operations/rule-configuration.md#load-rules) to load only the segments you do want on Historical processes. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
