​
Overview

Importing data from a CSV file into Oxla can be accomplished using various commands and tools. This guide covers different methods for copying data from a CSV file, including how to handle header rows, delimiters and defining null strings. Additionally, you will learn how to access cloud storage to copy tables, allowing you to migrate data from remote sources.

​
Syntax

The syntax for this function is as follows:

COPY table_name (column_name) FROM {'file_path' | STDIN} WITH (option);

​
Parameters

  • table_name: existing table where the data will be imported
  • column_name: optional list of columns to be copied
  • FROM: either file_path (path to your CSV file) or STDIN (data coming from the standard input)
  • option: one of the following to be selected:
    • FORMAT: format name (e.g. CSV)
    • DELIMITER: delimiter character represented as single quote string literal (by default, this function uses commas as the delimiter)
    • NULL: interpret the null value as ‘null_string’ (e.g. (NULL ‘unknown’))
    • HEADER: whether the CSV includes headers [ boolean | MATCH ]
    • Endpoint: provide object-based storage credentials

​
Examples

​
Manual Import via STDIN

1. Create a Table

Ensure the table exists in your Oxla instance. If the table does not exist, create one using the following command:

CREATE TABLE film (
    title text NOT NULL,
    length int,
    rating text
    );

You should see the output indicating that the table has been created.

2. Initiate Import

Run the following command to start the import operation:

COPY film FROM STDIN;

3. Enter Data

Paste the data from your CSV file into the prompt:

ATTRACTION NEWTON,83,PG-13
CHRISTMAS MOONSHINE,150,NC-17
DANGEROUS UPTOWN,121,PG
KILL BROTHERHOOD,54,G
HALLOWEEN NUTS,47,PG-13
HOURS RAGE,122,NC-17
PIANIST OUTFIELD,136,NC-17
PICKUP DRIVING,77,G
INDEPENDENCE HOTEL,157,NC-17
PRIVATE DROP,106,PG
SAINTS BRIDE,125,G
FOREVER CANDIDATE,131,NC-17
MILLION ACE,142,PG-13
SLEEPY JAPANESE,137,PG
WRATH MILE,176,NC-17
YOUTH KICK,179,NC-17
CLOCKWORK PARADISE,143,PG-13

If the import is successful, you will see IMPORT 0 at the end of the line, indicating no fatal error.

To end the import process on Unix-like systems, press Ctrl + D.

4. Verify Data

Query the table to ensure successful import:

SELECT * FROM film;

The output should be as follows:

    title          | length | rating 
--------------------+--------+--------
ATTRACTION NEWTON   |     83 | PG-13
CHRISTMAS MOONSHINE |    150 | NC-17
DANGEROUS UPTOWN    |    121 | PG
KILL BROTHERHOOD    |     54 | G
HALLOWEEN NUTS      |     47 | PG-13
HOURS RAGE          |    122 | NC-17
PIANIST OUTFIELD    |    136 | NC-17
PICKUP DRIVING      |     77 | G
INDEPENDENCE HOTEL  |    157 | NC-17
PRIVATE DROP        |    106 | PG
SAINTS BRIDE        |    125 | G
FOREVER CANDIDATE   |    131 | NC-17
MILLION ACE         |    142 | PG-13
SLEEPY JAPANESE     |    137 | PG
WRATH MILE          |    176 | NC-17
YOUTH KICK          |    179 | NC-17
CLOCKWORK PARADISE  |    143 | PG-13
(17 rows)

​
Importing Data via STDIN

Use the Meta-Command in psql to import the content of a CSV file directly into Oxla database. This method bypasses the need to manually enter data by reading the file and importing it directly into Oxla.

After launching the psql client application and creating the film table, follow these steps:

  1. Download the CSV file

You can use the film-dataset.csv file

2. Execute the Import Command

Run the query below:

COPY table_name FROM 'file_path' WITH (FORMAT CSV, HEADER ON);
  • Replace table_name with your target table name
  • Replace file_path with the full path to your CSV file
  • Use HEADER if your CSV file includes column headers

​
Importing Data via Piping

To import data into Oxla using the cat method, you’ll need to pipe the contents of your CSV file directly into the psql command. Make sure that your dataset is in a valid CSV format. After creating a table using psql, follow these steps:

1. Exit psql

Type \q followed by hitting the Enter button

2. Import the CSV File

Run the following command using your terminal:

cat /local/path/to/file.csv | psql -h localhost -U oxla oxla -c "COPY table_name FROM STDIN WITH (FORMAT CSV, HEADER ON);"

This command reads the contents of film-dataset.csv file and passes it directly to the COPY command.

​
Importing Data from Cloud Storage

To import data from an object storage into a table in Oxla, you can use the COPY FROM command with object storage credentials.nThis command allows you to transfer data from cloud storage services like AWS S3, Google Cloud Storage or Azure Blob Storage directly into your Oxla instance.

COPY table_name FROM 'cloud_storage_file_path' (object_storage(object_storage_credentials));
  • object storage: AWS_CRED,AZURE_CRED or GCS_CRED (depending on your provider)
  • object_storage_credentials: for accessing your cloud storage

You need to provide Provider-Specific credentials to authenticate access to your files. Use the following authentication parameters to access your cloud storage:

​
AWS S3 Bucket

  • aws_region: AWS region associated with the storage service
  • key_id: key identifier for authentication
  • access_key: access key for authentication
  • endpoint_url: URL endpoint for the storage service
COPY table_name FROM 's3://your-bucket/file_name' WITH (AWS_CRED(AWS_REGION 'us-west-1', AWS_KEY_ID 'key_id', AWS_PRIVATE_KEY 'access_key', ENDPOINT 's3.us-west-1.amazonaws.com'), FORMAT CSV, HEADER ON);

​
Google Cloud Storage

  • <path_to_credentials>: path to JSON credentials file
  • <json_credentials_string>: contents of the GCS’s credentials file
COPY table_name FROM 'gs://your-bucket/file_name' (GCS_CRED('/path/to/credentials.json'));
For Google Cloud Storage, it’s recommended to use HMAC keys for authentication. You can find more details about that on the HMAC keys - Cloud Storage page.

​
Azure Blob Storage

  • 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 for authentication
COPY table_name FROM 'wasbs://container-name/your_blob' (AZURE_CRED(TENANT_ID 'your_tenant_id' CLIENT_ID 'your_client_id', CLIENT_SECRET 'your_client_secret'));