Importing Data from a CSV file
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 importedcolumn_name
: optional list of columns to be copiedFROM
: eitherfile_path
(path to your CSV file) orSTDIN
(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:
You should see the output indicating that the table has been created.
2. Initiate Import
Run the following command to start the import operation:
3. Enter Data
Paste the data from your CSV file into the prompt:
If the import is successful, you will see IMPORT 0
at the end of the line, indicating no fatal error.
4. Verify Data
Query the table to ensure successful import:
The output should be as follows:
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:
- Download the CSV file
You can use the film-dataset.csv file
2. Execute the Import Command
Run the query below:
- 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:
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.
object storage
:AWS_CRED
,AZURE_CRED
orGCS_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 servicekey_id
: key identifier for authenticationaccess_key
: access key for authenticationendpoint_url
: URL endpoint for the storage service
Google Cloud Storage
<path_to_credentials>
: path to JSON credentials file<json_credentials_string>
: contents of the GCS’s credentials file
Azure Blob Storage
tenant_id
: tenant identifier representing your organization’s identity in Azureclient_id
: client identifier used for authenticationclient_secret
: secret identifier acting as a password for authentication