K
kvikesh
Guest
Progress DataDirect 5.1 PostgreSQL JDBC driver and 5.1 Greenplum JDBC driver now supports SQL COPY command supported by PostgreSQL and Greenplum database using a proprietary SQL escape to allow it to upload local file data to a database table and also fetch the database table data to a local file. Design of the feature has been posted earlier for review. Below is the link to the design CVP post: 2020.1 PostgreSQL/Greenplum JDBC: Insert/Fetch data in Local Files to/from Database Table with performance comparable to native driver CopyManager operations - Data Connectivity and SQL - DataDirect CVP - Progress Community Use Case (Insert): Use the modified SQL COPY SQL syntax to upload the local file data to PostgreSQL/Greenplum database table. Syntax: “COPY TargetTable (TargetColumn1, TargetColumns2, …) FROM {LOCALFILE ‘ ’} WITH option ….” Example: 1. “COPY Contacts (ContactID, ContactName, …) FROM {LOCALFILE ‘\usr\data\contactinfo’} WITH option ….” 2. "COPY COPY_TABLE_ALLTYPES FROM {LOCALFILE 'C:\Issues\Postgresql_COPY\ALL_TYPES_OUT.csv'} with DELIMITER ',' Please note the syntax used {LOCALFILE ‘ ’}. The driver will detect this escape syntax as a trigger to read the file and send it to the database as part of the COPY SQL execution. The options used in the copy syntax would be forwarded as it is to PostgreSQL/Greenplum server. The database server will process the file data according to these options and insert the data into the target table. Use Case (Fetch): Use the modified COPY SQL syntax to upload the local file data to PostgreSQL/Greenplum database table. Syntax: “COPY SourceTable (SourceColumn1, SourceColumns2, …) TO {LOCALFILE ‘ ’} WITH option ….” Example: 1. “COPY Conacts (ContactID, ContactName, …) TO {LOCALFILE ‘\usr\data\contactInfo’} WITH option ….” 2. COPY COPY_TABLE_ALLTYPES TO {LOCALFILE 'C:/Issues/Postgresql_COPY/ALL_TYPES_OUT.csv'} with DELIMITER ',' Please note the Escape Syntax used {LOCALFILE ‘ ’}. The driver will detect this escape syntax as a trigger to write the file locally. The database server will process the table data according to the options specified in the SQL query and covert into writable file bytes and send it to the driver. The driver will then read these bytes from the socket and write it to the local file identified by {LOCALFILE ‘ ’} escape syntax. The only difference between these two use cases is the keyword “FROM” and “TO” used to specify the direction of data movement. We did performance testing on the driver against native PostgreSQL driver COPY manager support. DataDirect driver is at par with native driver COPY manager support in throughput. An additional advantage apart from performance gain is that the JDBC application does not need to change and can adhere to JDBC specification. Performance Analysis Time Taken Driver COPY FROM COPY TO DataDirect 9.6 seconds (1 Million records) 7.6 seconds (1 Million records) 71.2 seconds (10 Million records) PostgreSQL Native 10.5 seconds (1 Million Records) 7.1 seconds (1 Million records) 71.2 seconds (10 Million records) Questions for Feedback: Are you seeing similar performance in your environment? Any challenges you might be facing using this functionality? Any suggestions on feature improvement. FTP Link to driver package, documentation, sample application: Link to User Guide - progress.thruinc.net/.../Link.aspx Link to Windows Installer - progress.thruinc.net/.../Link.aspx Link to Unix Installer – progress.thruinc.net/.../Link.aspx Link to PostgreSQL Sample application - progress.thruinc.net/.../Link.aspx Link to Greenplum Sample application - progress.thruinc.net/.../Link.aspx
Continue reading...
Continue reading...