A
Avadhoot Kulkarni
Guest
Feature Description: Currently, DataDirect PostgreSQL JDBC Driver Supports BatchMechnism=COPY to insert a large volume of data to PostgreSQL database Tables using JDBC Specification API of batchExecute. But this approach is slower than the Native drivers CopyManager utility, which can directly upload file data to a PostgreSQL database table using CopyManager.CopyIn() method. The proposal is to add an extension to 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. 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: “COPY Contacts (ContactID, ContactName, …) FROM {LOCALFILE ‘\usr\data\contactinfo’} WITH option ….” 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 TargetTable. Documentation of PostgreSQL syntax for COPY command 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, …) FROM {LOCALFILE ‘ ’} WITH option ….” Example: Sql= “COPY Conacts (ContactID, ContactName, …) TO {LOCALFILE ‘\usr\data\contactInfo’} WITH option ….” d 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. Please refer to the PostgreSQL documentation link above understand other details of the COPY SQL command including option clause. Documentation of PostgreSQL syntax for COPY command Download Information: This is a review request on the proposed design of this feature. Additional Information: PostgreSQL support COPY command is supported for PostgreSQL versions starting from 9.4. Syntax seems to vary slightly between versions. We will support the syntax specific to each version to define other aspects of copy command except the LOCALFILE escape syntax. As all of the processing is done by the database while fetching and inserting the data, the performance is expected to be comparable to the native CopyManager performance. CopyManager supports InputStream, Reader as valid Inputs and OutputStream or Writer as valid outputs. This allows CopyManager to work with various stream objects and Reader/Writer use cases. Our proposal only supports local files as input/output. Using files accessible to the server as input or output in the COPY SQL command is already supported using our driver and should be working properly even today. Questions for Feedback Do you see any challenges in adopting this feature implemented according to this proposal? Do you have any design level concerns on this proposal? Do you have any suggestion to make this feature more valuable to you? Does this proposal cover your major use cases?
Continue reading...
Continue reading...