CSV Exports - Civis API

Introduction

The CSV Exports API endpoint allows the user to export data by running a SQL Query and storing the results as Civis Files, or, alternatively, in an S3 Bucket of the user's choice.

Using the Endpoint

Instructions are provided below for the Civis Python client, but these methods are also available for the R client.

To get started creating a CSV Export Job you must provide the following parameters:

import civis
client = civis.APIClient()

export = client.exports.post_files_csv(
  source={
    "sql": "select 2", # The SQL query you want to run
    "remote_host_id": 32, # The ID of the remote host you want to query
    "credential_id": 61 # The ID of the credential of the above remote host
  }
)

printf('Export job ID is {export.id}')

# run the created job
future = civis.utils.run_job(export.id, client=client)

result = future.result()

To find the ID for your remote host, visit the Remote Hosts page and look for the name of your cluster. The type should be Redshift Cluster.

As for the credential ID please refer to the GET /credentials/ endpoint, which allows you to list all the credentials for a given remote host via the API.

Information about these and other, optional parameters can be found in the API Documentation for this endpoint.

Cached Results

CSV Exports take advantage of export caching to improve performance and reduce the load on your Platform data warehouse. Please see Export Caching for more information.

Using Your Own Bucket

By default, the CSV Exports API will store exported data as Civis Files. Once you run the job, you will get a link through which you can access these files.

However, if you have your own Amazon S3 Bucket, you can use it to store exported data. Before running your job, you will have to:

1. Register your S3 Bucket as a Storage Host on Platform.
   • Click on + New Service.
   • Select S3 Storage from the dropdown.
   • Enter a name and the url for your bucket.
   • Click Save.
2. Add an S3 key pair  for the bucket in Credentials. For more information on how to create a credential, please see this Helpdesk page. Your S3 key pair must have specific IAM permissions otherwise your CSV Export will fail. See this Helpdesk page for more information.

Once you've completed these steps you should have a Storage Host ID as well as a Credential ID for your bucket. Now you can amend your payload to include a specific destination:

import civis
client = civis.APIClient()

export = client.exports.post_files_csv(
  source={
    "sql": "select 2", # The SQL query you want to run
    "remote_host_id": 32, # The ID of the remote host you want to query
    "credential_id": 61 # The ID of the credential of the above remote host
  },
  destination={
    “storage_path”: {
      "storage_host_id": 100,  # The ID of the Storage host for your bucket
      "credential_id": 1000  # The ID of the Credential of your S3 key pair
    }
  }
)

print(f'Export job ID is {export.id}')

# run the created job
future = civis.utils.run_job(export.id, client=client)

result = future.result()