Exporting data using Druid SQL

Created with Sketch.

Exporting data using Druid SQL

0
(0)

Metatron distribution Druid provides a function to export JSON query results to files in various formats such as ORC, Parquet, JSON, CSV, Excel, etc. The syntax for this is specified in the context of the Druid JSON query and is as follows.

{
  "queryType": "groupBy",
  "dataSource": {
    "type": "table",
    "name": "covid_worldwide_test_data"
  },
  ...
  "outputColumns": [
    "Province_State",
    "SUM(positive)"
  ],
  "context": {
    "forwardContext": {
      "cleanup": "true",
      "columns": "Province_State,SUM(positive)",
      "format": "excel",
      "maxRowsPerSheet": 1000000
    },
    "forwardURL": "file:///var/folders/8l/fmdg5m9d5_3bx2zlyw3vc30w0000gn/T//MFD-709c2e76-f81a-4b34-aa29-66e54dca6349",
...
  }
}

The description and values ​​for each setting are as follows.

RequiredDescriptionValue
cleanupOSet whether to overwrite the data stored in forwardURLtrue | false
formatOFile format to exportexcel | csv | json | parquet | orc
forwardURLODirectory to save exported files
columnsOColumn list to be exported
It is specified among the names in ouputColumns.
maxRowsPerSheetNumber of records to be exported per sheet when format is excel
wrapAsListWhether to export the result in json list format when format is jsontrue | false
withHeaderWhether to include column name as header when format is csvtrue | false

The same results can be achieved using Druid SQL. Below is the syntax.

insert overwrite into directory '/Users/workspace/test' as 'ORC|PARQUET|CSV|JSON|EXCEL' with ('withHeader' => 'true'|'wrapAsList' => 'true'|maxRowsPerSheet => ‘50000’)

The following is an example of exporting an SQL query to EXCEL.

insert overwrite into directory '/Users/i1befree/workspace/test' as 'EXCEL' with ('maxRowsPerSheet' => '5000') 
SELECT * FROM "druid"."covid_worldwide_test_data"

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Share this post on your social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

 

Leave a Reply

Your email address will not be published. Required fields are marked *