Kinetica Input/Output (KIO)

Import (into Kinetica)

The Spark engine can transfer data from the following data format types to Kinetica:

• Apache Parquet (AWS S3 or local)
• CSV (AWS S3 or local)
• Microsoft SQL Server
• Oracle
• PostgreSQL
• Sybase IQ
• Teradata

The Odo engine can transfer data from the following data format types to Kinetica:

• Apache ORC
• Apache Parquet Dataset (AWS S3 or local)
• CSV (AWS S3 or local)
• Shapefile

Export (out of Kinetica)

The Spark engine can transfer data from Kinetica to the following data format types:

• PostgreSQL

The Odo engine can transfer data from Kinetica to the following data format types:

• Apache ORC

• Apache Parquet (local)

  Important

  The source data cannot be transferred to a local parquet file if the data contains bytes or timestamp data

• CSV (AWS S3, local)

• Microsoft SQL Server

• Oracle

• PostgreSQL

• Shapefile (AWS S3, local)

Usage

Base Kinetica URI:

kinetica://[[<username:password>]@][<host>:<port>]::[schema name.]<table name>

--d-base-type-overrides '{"column_name": "int"}'

--d-base-type-overrides '{"column_name": "string", "column_name2": "float"}'

--d-property-additions '{"column_name": ["data", "int16"]}'

--d-property-additions '{"column_name": ["char4"], "column_name2": ["data", "int16"]}'

--d-property-overrides '{"column_name": ["data", "int16"]}'

--d-property-overrides '{"column_name": ["char4"], "column_name2": ["data", "int16"]}'

[{"is_nullable": false, "column_properties":["data", "int16"], "column_type":"int", "column_name":"a"}]

Limitations and Cautions

• If a table does not exist when importing into Kinetica, the table will be created automatically and:

  • it will be created in the user's default schema
  • foreign keys are not preserved
  • distribution scheme is not preserved (unless using the --d-is-replicated option with spark as the --engine type)-- the table will be randomly sharded by default

• To remove properties, specify the --d-property-overrides option and list all properties that should be kept while leaving out any property(ies) that should be removed.

• Overriding a base type will remove its existing properties.

• If a table is being imported into Kinetica using the Spark engine and its name contains non-alphanumeric special characters, the special characters will be replaced with underscores

• If importing a GIS-type column(s) with the --gis-ext-enabled flag, the --d-property-additions parameter must be present containing a map of each GIS-type column to the wkt type, e.g.,

  --d-property-additions '{"gis_col1": ["wkt"], "gis_col2": ["wkt"]}'
  

Usage

Base Microsoft SQL Server URI:

sqlserver://<username>:<password>@<address><:port>/<database_name><::table_name>

Limitations and Cautions

• KIO does not support Java virtual machines (JVMs) with Federal Information Processing Standards (FIPS) enabled.
• KIO does not support extra properties for Microsoft SQL Server availability groups, including cluster or subnet failover.
• KIO does not support connecting to Microsoft SQL Server with SSL.

Usage

Base Oracle URI:

oracle://<username>:<password>@<address><:port>/<system_id><::schema_name>

Limitations and Cautions

• KIO uses the Oracle XE Service to establish a database connection. The listener.ora and tsnames.ora files must be correctly configured to expose the XE Service.

• KIO only supports Oracle 12c.

• When exporting from Oracle to a given destination, column names will be capitalized. When applying properties to a Kinetica destination table's column names, ensure the column names referenced are also capitalized, e.g.,:

  --d-property-additions '{"VENDOR_ID": ["text_search"]}'
  

Usage

Base PostgreSQL URI:

postgresql://<username>:<password>@<host><:port>/<database_name>[::schema_name]<::table_name>

Limitations and Cautions

• If a table does not exist when importing into PostgreSQL, the table will be created automatically. If attempting to copy from one table to another in PostgreSQL, the output table must already exist before KIO can copy the source table into it.
• Case sensitivity is disabled by default. When case sensitivity is disabled, PostgreSQL will query and reference table, column, and schema names by lowercase values.

Usage

Base Sybase IQ URI:

sybaseiq://<username>:<password>@<address><:port>/<database_name><::table_name>

Limitations and Cautions

• KIO will use the default character set of the connecting database; no conversion between character sets is available.
• KIO does not define Sybase IQ connection failover behavior.
• KIO does not define a timeout for client sessions or SQL queries.
• KIO does not support connecting with Sybase IQ using SSL.

Usage

Base Teradata URI:

teradata://<username>:<password>@<address><:port>/<database_name><::table_name>

Limitations and Cautions

• KIO only supports the UTF-8 character set (CHARSET) when connecting with Teradata.
• KIO does not support diagnostics or troubleshooting support via DDSTATS.
• KIO does not support data encryption between the Teradata JDBC driver and the Teradata database using ENCRYPTDATA.
• KIO does not support additional fractional digits.
• KIO does not support modifying users and authentication credentials using NEW_PASSWORD.
• KIO only supports Teradata versions 14.00, 14.10, 15.00, 15.10, 16.10, and 16.20.

Usage

Limitations and Cautions

• The following Apache ORC types are not currently supported by KIO when Kinetica is the destination:
  • struct
  • list
  • map
  • union
• KIO currently does not support referencing ORC files using relative file paths.

Usage

/path/to/directory/
├── example1.parquet
├── example2.parquet
├── subdirectory/
│   ├── example4.parquet
│   └── example5.parquet
└── example3.parquet

Limitations and Cautions

• KIO currently does not support referencing Parquet files using relative file paths.
• If the file does not exist when exporting to Parquet, it will be created. If the file does exist, it will be overwritten.
• KIO only supports hidden Parquet file ingestion (/file/path/.<file-name>.parquet) using the Odo engine

Usage

Limitations and Cautions

General:

• KIO currently does not support referencing Parquet Datasets using relative directory paths.
• KIO will always attempt to ingest the Parquet dataset as a nested directory of partitioned Parquet files. If ingesting a list of absolute file paths to Parquet files, move all the files into a single directory and use /file/path/*.parquet as the source URI instead to ingest the files as chunked Parquet files.
• KIO currently does not support inferring the types of partition keys, and as such, the keys are cast as strings. If ingesting the dataset into Kinetica, utilize --d-property-additions, --d-property-overrides, and --d-base-type-overrides to establish the data's types as desired. Refer to Kinetica for more information.
• KIO currently does not support reading in specific columns/partition keys from the Parquet Dataset.

AWS S3 Apache Parquet Dataset:

• KIO assumes that Parquet Datasets are not S3 buckets but rather a subdirectory (or subdirectories) within an S3 bucket
• Interacting with Apache Parquet Datasets in an S3 bucket is a Python 3-specific feature. Note that Python 3 is packaged with KIO by default

Usage

Limitations and Cautions

• If CSV is the destination and the target file(s) does not exist, column headers will be written to the file first and then data will be appended. If the target file(s) does exist, only data will be appended.

• If importing from a CSV into a non-existing table in Kinetica, the table will be created. The database will infer column base types and null values depending on the data being imported.

• KIO only supports wildcard ingestion of multiple CSVs (/file/path/*.csv) using the Spark engine

• KIO only supports hidden CSV file ingestion (/file/path/.<file-name>.csv) using the Odo engine

• If importing from a CSV into an existing table in Kinetica, the first row of the CSV file must contain a delimiter-separated (based on the user-specified delimiter) list of the column names in the order they appear in the existing table.

  Warning

  If the first row of the CSV file is a list of column names that don't match with the existing table, KIO will attempt to insert that row as data instead, which could result in import errors.

Usage

Limitations and Cautions

• The URI must reference the .shapefile file, which maps to a main file (.shp) and sometimes an index file (.shx) and a dBASE file (.dbf). KIO requires that at least the .shapefile and .shp files be available. These files (and any other files) must be in the same directory and have the same prefix to be recognized by KIO, e.g., usa-coastline.shapefile, usa-coastline.shp, etc.
• KIO currently does not support referencing shapefiles using relative file paths.
• Kinetica decimal fields are fixed precision and scale, so importing a shapefile that references numeric fields into Kinetica will be lossy
• KIO may truncate dBASE record values that are more than 256 characters long
• KIO supports wildcard ingestion of shapefiles (/file/path/*.shapefile) into the same destination data format provided that the schema for all files is identical, i.e. column names don't have to match but the rows of data therein must be of the same type. If a file's schema does not match the other files being ingested, the file will be ignored and the ingestion will continue. If the column names do not match, the first file ingested will determine the column names.
• Shapefiles can only be ingested using the Odo engine

Usage

Base S3 URI:

s3://<bucket name>/<file path>

Configuration

If not using the access key ID and secret access key command line options, your machine can be configured for direct AWS S3 access. Once you've created your AWS access key ID and AWS secret access key:

1. Install the official AWS CLI on your machine using pip:

   pip install awscli
   

2. Add your AWS account credentials to the machine. You can find the default region name in the URL for your AWS console; the default output format can be left as the default (None):

   $ aws configure
   AWS Access Key ID [None]: <access-key-id>
   AWS Secret Access Key [None]: <secret-access-key>
   Default region name [None]: <region-name>
   Default output format [None]:
   

3. Verify the .aws directory is available in your root directory:

   $ ls -al ~/
   total 500
   dr-xr-x---.  10 root root   4096 Jun 23 12:25 .
   dr-xr-xr-x.  17 root root   4096 Jun 23 11:49 ..
   drwxr-xr-x    2 root root     39 Jun 23 12:25 .aws
   ...
   

4. Verify you can read the contents of your AWS S3 bucket:

   aws s3 ls <your-bucket-name>
   

Limitations and Cautions

• If the file does not exist when exporting to S3, it will be created; if the file does exist, it will be overwritten.

• KIO supports wildcard ingestion of files on AWS S3 (e.g., s3://my-s3-bucket/*.csv, s3://my-s3-bucket/*.shapefile) into the same destination data format provided that the schema for all files is identical, i.e. column names don't have to match but the rows of data therein must be of the same type. If a file's schema does not match the other files being ingested, the file will be ignored and the ingestion will continue. If the column names do not match, the first file ingested will determine the column names

  Important

  If a prefix directory is provided, e.g., s3://my-s3-bucket/test/dir/*.csv, it will be recursively searched, and any .csv files found will being copied to the destination.

• The AWS credentials passed in via command line must have permissions to both the source and destination resources. KIO does not support separate AWS credentials for the source and destination resources.