COPY TO with Endpoint

​

Overview

When running COPY TO queries, you should have the option to include the endpoint URL. This feature is especially useful for scenarios where you need to provide credentials and specific endpoints.

​

Syntax

The syntax for using COPY TO statement is as follows:

COPY table_name TO 'file_path' (AWS_CRED(AWS_REGION 'aws_region', AWS_KEY_ID 
      'key_id', AWS_PRIVATE_KEY 'access_key', ENDPOINT 'endpoint_url'));

Here’s the breakdown of parameters syntax:

• Shared parameters:

  • table_name: table containing the data to be exported
  • file_path: CSV file location accessible from the server

• Parameters in AWS_CRED:

  • aws_region: AWS region associated with the storage service (e.g. ‘region1’)
  • key_id: key identifier used for authentication
  • access_key: access key used for authentication
  • endpoint_url: URL endpoint for the storage service

• Parameters in GCS_CRED:

  • <path_to_credentials>: path to JSON credentials file.

• Parameters in AZURE_CRED:

  • tenant_id: tenant identifier representing your organization’s identity in Azure
  • client_id: client identifier used for authentication.
  • client_secret: secret identifier acting as a password when authenticating

​

Examples

​

COPY TO with AWS S3 Bucket

In this example, we use the COPY TO statement to export data from the students table to a CSV file named students_file.

COPY students TO 's3://oxla-testdata/cayo/students_file' (AWS_CRED(AWS_REGION 'region1', AWS_KEY_ID 
      'key_id', AWS_PRIVATE_KEY 'access_key', ENDPOINT 's3.us-east-2.amazonaws.com'));

Expected Output: student table data is copied to the students_file on AWS S3

​

COPY TO with Google Cloud Storage

This example shows how to use the COPY TO statement to export data, but this time, the data is stored on Google Cloud Storage.

COPY project TO 'gs://your-bucket/project_file' (GCS_CRED('/path/to/credentials.json'));

You can also copy the data using the AWS_CRED like below:

COPY project TO 'gs://your-bucket/project_file' (AWS_CRED(AWS_REGION 'region1', AWS_KEY_ID 
      'key_id', AWS_PRIVATE_KEY 'access_key', ENDPOINT 'https://storage.googleapis.com'));

Expected Output: Data from the project table is copied to the project_file on Google Cloud Storage

​

COPY TO with Azure BlobStorage

It’s a similar story for storing data in Azure Blob Storage.

COPY taxi_data TO 'wasbs://container-name/your_blob' (AZURE_CRED(TENANT_ID 'your_tenant_id' CLIENT_ID 'your_client_id', CLIENT_SECRET 'your_client_secret'));

Expected Output: Data from the taxi_data table is copied to your_blob on Azure Blob Storage