Parquet has a columnar data format that is particularly beneficial for analytics workloads where queries often need to access only a subset of the columns in a dataset, making it a favored option in numerous data pipeline strategies.
The Amazon S3 parquet export integration lets you generate parquet files from Treasure Data job results and upload them directly to Amazon S3.
- Basic knowledge of Treasure Data.
If you are using an AWS S3 bucket located in the same region as your TD region, the IP address from which TD is accessing to the bucket will be private and dynamically changing. If you would like to restrict access, please specify the ID of VPC instead of static IP Addresses. For example, if in the US region, configure access through vpc-df7066ba. If in the Tokyo region, configure access through vpc-e630c182 and, for the EU01 region, vpc-f54e6a9e.
Look up the region of TD Console by the URL you are logging in to TD, then refer to the data connector of your region in the URL.
See the API Documentation for details.
If your security policy requires IP whitelisting, you must add Treasure Data's IP addresses to your allowlist to ensure a successful connection.
Please find the complete list of static IP addresses, organized by region, at the following link:
https://api-docs.treasuredata.com/en/overview/ip-addresses-integrations-result-workers/
- Only supports maximum row group size of 512 MB.
- As multipart upload is used to upload files, some upload parts may remain in the S3 containers for some reason. It is recommended to enable a clean-up policy for incomplete multipart uploads. For details, see S3 Lifecycle Management Update – Support for Multipart Uploads and Delete Markers.
You must create and configure the data connection before running your query. As part of the data connection, you provide authentication to access the integration.
| Authentication Method | Amazon S3 Parquet |
|---|---|
| basic | x |
| session | x |
| assume_role | x |
Your first step is to create a new authentication with a set of credentials.
- Select Integrations Hub.
- Select Catalog.
- Search for your Integration in the Catalog; hover your mouse over the icon and select Create Authentication.
- Ensure that the Credentials tab is selected and then enter credential information for the integration. The Authentication fields
| Parameter | Description |
|---|---|
| Endpoint | S3 service endpoint override. You can find region and endpoint information in AWS service endpoints. (Ex. s3.ap-northeast-1.amazonaws.com) When specified, it will override the region setting. |
| Region | AWS Region |
| Authentication Method | |
| basic |
|
| session (Recommended) |
|
| assume_role |
|
| anonymous | Not Supported |
| Access Key ID | AWS S3 issued |
| Secret Access Key | AWS S3 issued |
| Session Token | Your temporary AWS Session Token |
| TD's Instance Profile | This value is provided by the TD Console. The numeric portion of the value constitutes the Account ID that you will use when you create your IAM role. |
| Account ID | Your AWS Account ID |
| Your Role Name | Your AWS Role Name |
| External ID | Your Secret External ID |
| Duration | Duration For The Temporary Credentials |
- Select Continue.
- Enter a name for your authentication, and select Done.
- Create a new authentication with the assume_role authentication method.
- Make a note of the numeric portion of the value in the TD's Instance Profile field.
- Create your AWS IAM role.
- Navigate to Data Workbench > Queries.
- Select New Query.
- Run the query to validate the result set.
- Select Export Results.
- Specify an Authentication, and start configuring the export. You can select an existing authentication or create a new authentication for the external service to be used for output. Choose one of the following:
Use an existing Integration
Create a new Integration
**
| Field | Description |
|---|---|
| Server-side encryption | Support values: - sse-s3: Server-side Encryption Mode - sse-kms: new SSE Mode |
| Server-side Encryption Algorithm | Support value: - AES256 |
| KMS Key ID | Symmetric AWS KMS Key ID. If there is no input for the KMS Key ID, it will create/use the default KMS Key. |
| Bucket | Provide the S3 bucket name (Ex., your_bucket_name). |
| Compression | Provide the S3 bucket name (Ex., your_bucket_name). |
| Path | Specify the s3 filename (object key), and include an extension (Ex. test.parquet). |
| Compression | The compression format of the exported files. Support value: - None - Gzip - Snappy |
| Row group size | Specify the group size of the parquet file |
| Page size | Specify the page size of the parquet file |
| Timestamp Unit | Specify the timestamp format for parquet file. Supported time units: - Milliseconds - Microseconds |
| Single file | Uncheck it to split into multiple files with the size of a Row Group |
| Enable Bloom filter | Enable Bloom filter for parquet file |
| Retry limit | Maximum retry times |
| Retry wait | Waiting time in ms for each retry time |
| Number of concurrent threads | Number of concurrent threads for S3 threads to upload |
| Connections per threads | Number of HTTP connections per thread open to S3 |
| Part size | Part size of multipart upload for S3 |
You can create an activation to export segment data or stages.
You can use Scheduled Jobs with Result Export to periodically write the output result to a target destination that you specify.
Treasure Data's scheduler feature supports periodic query execution to achieve high availability.
When two specifications provide conflicting schedule specifications, the specification requesting to execute more often is followed while the other schedule specification is ignored.
For example, if the cron schedule is '0 0 1 * 1', then the 'day of month' specification and 'day of week' are discordant because the former specification requires it to run every first day of each month at midnight (00:00), while the latter specification requires it to run every Monday at midnight (00:00). The latter specification is followed.
Navigate to Data Workbench > Queries
Create a new query or select an existing query.
Next to Schedule, select None.
In the drop-down, select one of the following schedule options:
Drop-down Value Description Custom cron... Review Custom cron... details. @daily (midnight) Run once a day at midnight (00:00 am) in the specified time zone. @hourly (:00) Run every hour at 00 minutes. None No schedule.
| Cron Value | Description |
|---|---|
0 * * * * | Run once an hour. |
0 0 * * * | Run once a day at midnight. |
0 0 1 * * | Run once a month at midnight on the morning of the first day of the month. |
| "" | Create a job that has no scheduled run time. |
* * * * *
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (Sunday=0)
| | | +---------- month (1 - 12)
| | +--------------- day of month (1 - 31)
| +-------------------- hour (0 - 23)
+------------------------- min (0 - 59)The following named entries can be used:
- Day of Week: sun, mon, tue, wed, thu, fri, sat.
- Month: jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec.
A single space is required between each field. The values for each field can be composed of:
| Field Value | Example | Example Description |
|---|---|---|
| A single value, within the limits displayed above for each field. | ||
A wildcard '*' to indicate no restriction based on the field. | '0 0 1 * *' | Configures the schedule to run at midnight (00:00) on the first day of each month. |
A range '2-5', indicating the range of accepted values for the field. | '0 0 1-10 * *' | Configures the schedule to run at midnight (00:00) on the first 10 days of each month. |
A list of comma-separated values '2,3,4,5', indicating the list of accepted values for the field. | 0 0 1,11,21 * *' | Configures the schedule to run at midnight (00:00) every 1st, 11th, and 21st day of each month. |
A periodicity indicator '*/5' to express how often based on the field's valid range of values a schedule is allowed to run. | '30 */2 1 * *' | Configures the schedule to run on the 1st of every month, every 2 hours starting at 00:30. '0 0 */5 * *' configures the schedule to run at midnight (00:00) every 5 days starting on the 5th of each month. |
A comma-separated list of any of the above except the '*' wildcard is also supported '2,*/5,8-10'. | '0 0 5,*/10,25 * *' | Configures the schedule to run at midnight (00:00) every 5th, 10th, 20th, and 25th day of each month. |
- (Optional) You can delay the start time of a query by enabling the Delay execution.
Within Treasure Workflow, you can specify the use of this integration to export data.
_export:
td:
database: td.database
+a_s3_parquet_export_task:
td>: export_test.sql
database: ${td.database}
result_connection: s3_parquet_conn
result_settings:
type:To output the result of a single query to an S3 bucket, add the --result option to the td query command. After the job is finished, the results are written into your S3 bucket. About the td query command, you can refer to this article.
You can specify detailed settings to export your S3 via the --result parameter as follows:
{
"type": "s3_parquet",
"endpoint": "",
"region": "us-east-1",
"auth_method": "",
"session_token": "",
"account_id": "",
"role_name": "",
"duration_in_seconds": 3600,
"access_key_id": "xxxxx",
"secret_access_key": "xxxxx",
"sse_type": "sse-s3",
"sse_algorithm": "AES256",
"kms_key_id": "arn:aws:kms:us-east-1:xxx:key/xxx",
"bucket": "bucket",
"path": "file.parquet",
"row_group_size": 256,
"page_size": 1024,
"timestamp_unit": "milliseconds",
"enable_bloom_filter": false,
"enable_single_file": true,
"compression": "gzip",
"part_size": 100,
"retry_limit": 7,
"retry_wait_millis": 500,
"number_of_concurrent_threads": 4,
"connections_per_thread": 128,
}| Name | Description | Value | Default Value | Required |
|---|---|---|---|---|
| type | Describe the name of the service as the destination of export. | s3_parquet | N/A | Yes |
| endpoint | S3 service endpoint override. You can find region and endpoint information in AWS service endpoints. (Ex. s3.ap-northeast-1.amazonaws.com) | S3 service endpoint override | N/A | No |
| region | AWS Region | AWS Region | us-east-1 | No |
| auth_method | Authentication method for S3 | basic session assume_role | basic | Yes |
| access_key_id | Access Key ID | Key ID | N/A | If you use auth_method as basic or session |
| secret_access_key | Secret Access Key | Secret access key | N/A | If you use auth_method as basic or session |
| session_token | Session Token | Session Token | N/A | If you use auth_method as session |
| account_id | Account ID | Account ID | N/A | If you use auth_method as assume_role |
| role_name | Role Name | Role Name | N/A | If you use auth_method as assume_role |
| external_id | External ID | External ID | N/A | If you use auth_method as assume_role |
| duration_in_seconds | Duration of the connection | Duration in seconds | 3600 | No |
| bucket | S3 bucket value | S3 bucket value | N/A | Yes |
| path | S3 path with file format, ex: "/path/file.parquet" | Path file | N/A | Yes |
| row_group_size | Parquet row group size | Parquet row group size | 256 | No |
| page_size | Parquet page size | Parquet page size | 1024 | No |
| timestamp_unit | Parquet timestamp format | - milliseconds - microseconds | milliseconds | No |
| enable_bloom_filter | Enable bloom filter for parquet file | - true - false | false | No |
| enable_single_file | Enable export to a single file or multiple files with one row_group_file in each file | - true - false | true | Y |
| compression | Compression format of the exported file. | - None - Gzip - Snappy | None | No |
| retry_limit | Maximum retry times | Retry limit | 7 | No |
| retry_wait_millis | Waiting time in ms for each retry time | Retry wait in ms | 500 | No |
| number_of_concurrent_threads | Number of concurrent threads for S3 threads to upload | Concurrent S3 threads | 4 | No |
| connection_per_thread | Number of HTTP connections per thread open to S3 | Connection HTTP per thread | 16 | No |
| part_size | Part size of multiple part upload for S3 | Part size | 100 | No |
$ td query \
--result '{"type":"s3_parquet","auth_method":"basic","access_key_id":"access_key_id","secret_access_key":"secret_access_key","bucket":"bucket","path":"/path/file.parquet"}' \
-d sample_datasets "select * from www_access" -T presto