External Databases

The SQLoader is a CLI program that enables you to load data into SQreamDB from other databases.

SQLoader supports Oracle and Postgresql.

Before You Begin

It is essential that you have the following:

• Java 17

• SQLoader configuration files

• SQLoader.jar file

Getting the SQLoader Configuration and JAR Files

1. Download the .tar file using the following command:

curl -O https://sq-ftp-public.s3.amazonaws.com/sqloader-7.0.tar

2. Extract the .tar file using the following command:

tar -xf sqloader-7.0.tar.gz

A folder named sqloader with the following files is created:

SQLoader Files

File	Description
sqream-mapping.json	Maps Postgresql and Oracle data types into SQreamDB data types during ingestion
sqload-jdbc.properties	Used for defining a connection string and may also be used to reconfigure data loading
reserved_words.txt	A list of reserved words which cannot be used as table and/or column names
sqloader.jar	The SQLoader package file

Connection String

The sqload-jdbc.properties file contains a connection string that must be configured to enable data loading into SQreamDB.

Configuring SQLoader Connection

1. Open the sqload-jdbc.properties file.

2. Configure the following parameters for:

   1. Either Postgresql, Oracle, or SQreamDB connection strings

   2. Optionally, Oracle or SQreamDB catalogs (recommended)

Connection String Parameters

Parameter	Description
HostIp:port	The host and IP address number
database_name	The name of the database from which data is loaded
user	Username of a role to use for connection
password	Specifies the password of the selected role
ssl	Specifies SSL for this connection

Loading Data into SQreamDB Tables

1. Run the sqloader.jar file using the following CLI command:

java -jar sqloader.jar

2. You may load the entire data of a source table using the following CLI command:

java -jar sqloader.jar -table source_table_name

3. You may customize the data load either by using each of the following parameters within a CLI command or by configuring the properties file:

SQLoader CLI Parameters

CLI Parameter	State	Default	Description
-batchsize	Optional	10.000	The number of records to be inserted into SQreamDB at once. Please note that the configured batch size may impact chunk sizes.
-casesensative	Optional	false	If true, keeps table name uppercase and lowercase characters when table is created in SQreamDB
-check_cdc_chain	Optional	false	Check CDC chain between tracking table and source table
-chunkSize	Optional	0	The number of records read at once from the source database
-columnlist	Optional	None	The name of the file that contains all column names. Columns must be separated using \n
-columns	Optional	All columns	The name or names of columns to be loaded into SQreamDB (“col1,col2, …”)
-config	Optional	config/sqload-jdbc.properties	Defines the configuration file you wish to use
-count	Optional	true	Defines whether or not table rows will be counted before being loaded into SQreamDB
-delete	Optional	true	Defines whether or not loading using Change Data Capture (CDC) includes deleted rows
-drop	Optional	true	Defines whether or not a new target table in SQreamDB is created. If false, you will need to configure a target table name using the -target parameter
-fetchsize	Optional	100000	The number of records to be read at once from source database.
-filter	Optional	1=1	Defines whether or not only records with SQL conditions are loaded
-h, --help	Optional	None	Displays the help menu and exits
-limit	Optional	0 (no limit)	Limits the number of rows to be loaded
-load_dttm	Optional	true	Add an additional load_dttm column that defines the time and date of loading
-lock_check	Optional	true	Defines whether or not SQLoader will check source table is locked before the loading starts
-lock_table	Optional	true	Defines whether or not SQLoader will lock target table before the loading starts
-log_dir	Optional	logs	Defines the path of log directory created when loading data. If no value is specified, a logs folder is created under the same location as the sqloader.jar file
-partition	Optional	None	Defines the partition to load data from if table is partitioned
-rowid	Optional	false	Defines whether or not SQLoader will get row IDs from Oracle tables
-source_db	Optional	ORCL	Defines the source database name. It does not modify the database connection string but impacts the storage and retrieval of data within catalog tables.
-split	Optional	None	Column name for split (required for multi-thread loads)
-table	Mandatory	None	Source table name to load data from
-target	Optional	Source table name	Target table name to load data into
-target_db	Optional	None	Target database name to load data into
-thread	Optional	1	Number of threads to be used during loading
-truncate	Optional	false	Truncate target table before loading
-type	Optional	full	Defines a loading type that affects the table that is created in SQreamDB. Options are full, cdc, or inc. Please note that cdc, and inc are supported only for Oracle
-use_dbms_lob	Optional	true	Defines whether or not SQLoader uses dbms_lob_substr function for CLOB and BLOB data types
-use_partitions	Optional	true	Defines whether or not SQLoader uses partitions in SELECT statements

Using the type Parameter

Using the type parameter you may define a loading type that affects the table that is created in SQreamDB.

Loading Type	Parameter Option	Description
Full Table	full	The entire data of the source table is loaded into SQreamDB
Change Data Capture (CDC)	cdc	Only changes made to the source table data since last load will be loaded into SQreamDB. Changes include transactions of INSERT, UPDATE, and DELETE statements. SQLoader recognizes tables by table name and metadata. Supported for Oracle only
Incremental	inc	Only changes made to the source table data since last load will be loaded into SQreamDB. Changes include transactions of INSERT statement. SQLoader recognizes the table by table name and metadata. Supported for Oracle only

Data Type Mapping

The SQLoader automatically maps data types used in Postgresql and Oracle tables that are loaded into SQreamDB.

Postgresql

Postgresql Type	SQreamDB Type
CHAR, VARCHAR, CHARACTER	TEXT
TEXT	TEXT
INT, SMALLINT, BIGINT, INT2, INT4 INT8	BIGINT
DATETIME, TIMESTAMP	DATETIME
DATE	DATE
BIT, BOOL	BOOL
DECIMAL, NUMERIC	NUMERIC
FLOAT, DOUBLE	DOUBLE
REAL, FLOAT4	REAL

Oracle

Oracle Type	SQreamDB Type
BIGINT, INT, SMALLINT, INTEGE	BIGINT
CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR, CHARACTER	TEXT
DATE, DATETIME	DATETIME
TIMESTAMP	DATETIME
DATE	DATE
BOOLEAN	BOOL
NUMERIC	NUMERIC
FLOAT, DOUBLE	DOUBLE
CLOB	TEXT
BLOB	TEXT

CLI Examples

Loading data into a CDC table using the type and limit parameters:

java -jar sqloader.jar -table source_table_name -type cdc -limit 100

Loading data into a table using your own configuration file (this will override the default configuration file):

java -jar sqloader.jar -config path/to/your/config/file

Loading data into a table using a custom configuration file:

java -jar -config MyConfigFile.properties -table source_table_name -type cdc -target target_table_name -drop true -lock_check false

Loading data into a table using a the filter parameter:

java -jar sqloader.jar -table source_table_name -filter column_name>50