ExportSchemaUdp
From Eigenpedia
NOTE: usage and syntax documentation for the export schema UDPs is being transferred over to the luciddb system procedures wiki page
This page specifies the usage and proposed implementation for the Export foreign schema and local schema procedures.
Contents |
Intro
Luciddb provides procedures to export tables from a foreign data server or a local schema into flat files. Associated BCP files can also be created. A foreign data wrapper and server will be used to access the foreign data and need to be created prior to invoking the procedure.
Note: the BCP files created are not standard. They have luciddb flatfile specific data within to ensure that the tables and column datatypes are the same when importing the data back into a farrago/luciddb database . An option to create BCP files which are standard but which may not produce identical tables when the data is imported can be added if needed.
Export Foreign Schema
Usage
CALL SYS_ROOT.EXPORT_FOREIGN_SCHEMA_TO_CSV( 'foreign_data_server', 'foreign_schema', exclude, 'table_list', 'table_pattern', 'directory' , with_bcp, delete_failed_file )
Parameters
- foreign_data_server – name of the foreign server.
- foreign_schema_name – name of the foreign schema. The browse_foreign_schemas UDX can be used to determine the foreign schemas accessible for a given foreign server.
- exclude - if true, tables matching either the table_list of the table_pattern will be excluded. if false, tables will be included. [boolean]
- table_list - comma separated list of tables or null value if table_pattern is being used.
- table_pattern – table name pattern where '_' represents any single character and '%' represents any sequence of zero or more characters. Set to null value if table_list is being used.
- directory – the directory in which to place the exported CSV and BCP files.
- with_bcp – indicates whether bcp files should be created. if true, bcp files will be created. If false, they will not be created. [boolean]
- delete_failed_file - indicates whether incomplete csv files for tables which failed export should be deleted. If true, failed files will be deleted, if false they will not. [boolean]
Tai> The directory is on the host system where Luciddb server runs. In order to restrict database users from
Tai> perhaps we can introduce a luciddb system
Tai> directory under $LUCIDDB_HOME/exportDir as the root directory for all
Tai> export files. This the 'directory' specified in the call to this udp will be created under the this system directory.
JVS: If we want that kind of security, we can probably do it via a generic sandboxing feature, e.g. we associate a SecurityManager with each user and use that to enforce whatever constraints we want.
Only one of the parameters table_list and table_pattern can be specified during a procedure call. The other value must be null. If both parameters are set to null, then no filtering will occur and exclude parameter is ignored.
Export Local Schema
There are two procedures availiable, one which includes the options:
- To specify the column field delimiter to be used within the flat files created, and
- To specify the file extension to be used for the flat files created.
Usage
CALL SYS_ROOT.EXPORT_SCHEMA_TO_CSV('catalog', 'schema', exclude, 'table_list', 'table_pattern', 'directory', with_bcp , delete_failed_file )
CALL SYS_ROOT.EXPORT_SCHEMA_TO_FILE('catalog', 'schema', exclude, 'table_list', 'table_pattern', 'directory', with_bcp, delete_failed_file, 'field_delimiter', 'file_extension' )
Parameters
- catalog – name of the catalog where schema resides, if null, uses default/current catalog name.
- schema – name of the local schema.
- exclude - if true, tables matching either the table_list or the table_pattern will be excluded. if false, tables will be included. [boolean]
- table_list - comma separated list of tables or null value if table_pattern is being used.
- table_pattern – table name pattern where '_' represents any single character and '%' represents any sequence of zero or more characters.
- directory – the directory in which to place the exported CSV and BCP files.
- with_bcp – indicates whether BCP files should be created. If true, BCP files will be created. If false, they will not be created. [boolean]
- delete_failed_file - indicates whether incomplete csv files for tables which failed export should be deleted. If true, failed files will be deleted, if false they will not. [boolean]
- field_delimiter - the character used as a delimiter for the table column fields within each of the flat files created. Common delimiters could be commas ',' or tabs '\t' Field delimiters are limited to 2 characters.
- file_extension - the file extension to use for each of the flat files created. Common file extensions could be '.txt' or '.csv' File extension is limited to 5 characters. (ex. '.xxxx')
Only one of the parameters table_list and table_pattern can be specified during a procedure call. The other value must be null. If both parameters are set to null, then no filtering will occur and exclude parameter is ignored.
Implementation
Both UDPs can be added to localdb.sys_root. For the local schema export UDP, connect to the database using jdbc:default:connection and use the database metadata to find out what tables are in the specified catalog/schema. Then query to get a resultset for each of the tables one by one, outputting the data to the csv file and using the resultset metadata to create the bcp file.
The foreign schema export UDP will connection back into the database and create a temporary schema to import the foreign schema into. Then it can call the green zone UDP to export all tables within the temporary schema before dropping the temporary schema.
