SQL Statements

Owned by Yeok Hong Ng
Apr 26, 2024
2 min read

The format of the SQL statements differs depending on which database type you are using.

For MySQL, Netezza, PostgreSQL, SybaseIQ and Vertica, the path to the file (stated as <path to file> in the code blocks below) can be entered in three different ways:

  • Entering the path explicitly, e g 'Users/mine/mypath'

  • Stating the UDR field containing the path by entering $(UDR.fullpath)

  • Stating the MIM value containing the path, e g $(Source_Pathname) for the Disk agent

MySQL

For remote loading (the file resides in a local directory)

LOAD DATA LOCAL INFILE <path to file> INTO TABLE TABLENAME FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

For server-side loading (file resides in the server file system of the database)

LOAD DATA INFILE <path to file> INTO TABLE TABLENAME FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Netezza

For remote loading (the file resides in a local directory)

INSERT INTO TABLENAME SELECT * FROM EXTERNAL <path to file> USING (delim ',' REMOTESOURCE 'JDBC')

For server-side loading (file resides in the server file system of the database)

PostgreSQL

For server-side loading (file resides in the server filesystem of the database)

Stating columns is optional.

Note!

Postgres does not support remote file loading.

SAP HANA

For server-side loading (file resides in the server file system of the database)

  1. Create a control file containing code below (this example ctl file name is abc.ctl, abc.txt is the csv file): 

     

  2. Run the workflow with the following command: 



Note!

SAP HANA does not support remote file loading. 

Sybase IQ

For server-side loading (file resides in the server file system of the database)



Note!

The Sybase JConnect driver does not support remote file loading.

Vertica

For server-side loading (file resides in the server file system of the database)

If the file contains a header, you can add SKIP 1 to the SQL query, like so: