Connecting to Multiple S3 Locations in Drill

Document created by mufeed Employee on Feb 13, 2016
Version 1Show Document
  • View in full screen mode

Author: Mufeed Usman

 

Original Publication Date: March 26, 2015

 

How to connect to multiple S3 locations using Drill (with different set of secret and access keys)?

 

Use-case

  • To join two different files in two different S3 locations with different S3 credentails.

 

You can specify multiple “file” type storage plugins. One for each S3 instance and then join them.

 

Add the auth keys in the "file" specification" configuration of the storage plugins as shown below:

s3test1:

{

  "type": "file",

  "enabled": true,

  "connection": "s3n://<accesskey1>:<secret1>@<username1>",

  "workspaces": { },

  "formats": {

  "psv": { "type": "text", "extensions": [ "tbl" ], "delimiter": "|" },

  "csv": { "type": "text", "extensions": [ "csv" ], "delimiter": "," },

  "tsv": { "type": "text", "extensions": [ "tsv" ], "delimiter": "\t" },

  "parquet": { "type": "parquet" },

  "json": { "type": "json" }

  }

}

 

s3test2:

{

  "type": "file",

  "enabled": true,

  "connection": "s3n://<accesskey2>:<secret2>@<username2>",

  "workspaces": { "root": { "location": "/yelp", "writable": false, "defaultInputFormat": null } },

  "formats": {

  "psv": { "type": "text", "extensions": [ "tbl" ], "delimiter": "|" },

  "csv": { "type": "text", "extensions": [ "csv" ], "delimiter": "," },

  "tsv": { "type": "text", "extensions": [ "tsv" ], "delimiter": "\t" },

  "parquet": { "type": "parquet" },

  "json": { "type": "json" }

  }

}

For example, I have the yelp_academic_dataset_business.json file in s3test1 and the yelp_academic_dataset_review.json in s3test2. The JOIN can be carried out as follows,

0: jdbc:drill:zk=172.16.1.175:5181,172.16.1.1> select a.`name`, b.`text` FROM s3test1.`default`.`yelp1/yelp_academic_dataset_business.json` a JOIN s3test2.`default`.`yelp/yelp_academic_dataset_review.json` b ON a.`business_id` = b.`business_id` limit 1;

+------------+------------+

| name | text |

+------------+------------+

| Thai Pan Fresh Exotic Cuisine | Lately i have been feeling homesick

for asian food and been hitting up places that i haven't been to in

awhile. Recently re-visited Thai Pan for a quick lunch and quickly

ordered without spending too much time perusing the menu. It looked

more diverse than I remembered including some Vietnamese additions. I

remembered the curries and stir-fry dishes were ok but nothing really

memorable. A quick summary for my latest visit:

 

Pros:

- convenient order-at-the counter setup

- self-serve drink station

- brown and white rice mixture

- friendly and gracious owners

 

Cons:

- too much napa cabbage in comparison to green vegetables

- wish the owner/chef would be back in the kitchen vs. managing

- spice level on the weak side |

+------------+------------+

1 row selected (6.606 seconds)

 

Attachments

    Outcomes