Load Data from JDBC
Ingest from any compatible source using a JDBC driver
Ingest from any compatible source using a JDBC driver
Loading data from a JDBC data source can be done in two easy steps:
To illustrate data ingress from a JDBC data source, let's look at an example that uses PostgreSQL. If you would like to follow along with the examples in this article, you will need:
Kinetica provides many service provider JDBC drivers in a default installation. Drivers not on the supported list can be uploaded manually.
Since the PostgreSQL driver is already available in Kinetica, it's time to create a data source. Use the PostgreSQL connection string on the JDBC driver list and the linked documentation to construct a connection string for the LOCATION.
Note
Kinetica's native JDBC ingress differs from other native ingress modes in that you do not need to first create a credential. Authentication parameters can be passed directly into the data source at the time of creation.
|
|
Now that we have created a data source, let's ingest the data. The query specified in the REMOTE QUERY statement will be executed on the data source and it defines the data that Kinetica will receive in return.
|
|
To load only the rows that satisfy a condition, you can use a WHERE clause in the REMOTE QUERY like this:
|
|
The following are some common options used when loading data. For the full list of options, see LOAD INTO. For copy/paste examples of many of the options, see Loading Data.
Kinetica has two different error handling modes for dealing with erroneous data. To halt ingestion after a bad record is found, use the ABORT mode. To skip erroneous records and continue the ingest, use the SKIP mode.
To inspect erroneous records, you may use the BAD RECORD TABLE NAME option. All bad records encountered will be stored there for review. The bad records table is limited to 10,000 records by default and may be overridden using the BAD RECORD TABLE LIMIT option.
|
|
|
|
If the service you need to connect to is not on Kinetica's supported list, you will need to upload a driver for that service into KiFS before creating a data source.
For these instructions, we will upload a custom PostgreSQL driver into a directory in KiFS called drivers and remove the version from the file name. The final file path will be: kifs://drivers/postgresql.jar. When creating a data source with a user-provided driver, this KiFS path will be specified for the JDBC_DRIVER_JAR_PATH option. Consult the service's documentation for the JDBC class name to specify for the JDBC_DRIVER_CLASS_NAME option.
To upload the driver using Kinetica's Workbench UI:
To upload the driver using KiSQL, Kinetica's CLI SQL interface, issue the following commands.
It is assumed that the driver jar is located in the same directory as KiSQL.
|
|
|
|
|
|