このプロシージャにより、ユーザーは表の内容をCSV 形式でエクスポートできます。

Parameters

Parameter

Description

sourceSchema

Source schema in the Data Virtuality Server; mandatory

sourceTable

Source table in the Data Virtuality Server; mandatory

targetSchema

 File data source in the DataVirtuality Server. The file will be stored in the directory assigned to the file data source; mandatory

targetFile

Name of the file to store the exported data. If a file with the same name already exists, it will be overwritten. If omitted, name will be created: sourceSchema_sourceTable.csv

delimiter

If not specified otherwise, comma(,) is used as the delimiter by default

quote

If not specified otherwise, double quotes("") are used as the quote character by default. All values will be quoted

header

If specified, the result contains the header row as the first line - the header line will be present even if there are no rows in a group

orderBy

May be used to sort the output

encoding

Encoding for the created file. The system's default encoding is used by default

Usage

CALL "UTILS.csvExport"(
"sourceSchema" => 'string_sourceSchema',
"sourceTable" => 'string_sourceTable',
"targetSchema" => 'string_targetSchema',
"targetFile" => 'string_targetFile',
"delimiter" => 'string_delimiter',
"quote" => 'string_quote',
"header" => boolean_header,
"orderBy" => 'string_orderBy',
"encoding" => 'string_encoding'
);;

Definition

CREATE VIRTUAL PROCEDURE csvExport
(
IN sourceSchema string NOT NULL OPTIONS (ANNOTATION 'The source schema in Data Virtuality Server.')
, IN sourceTable string NOT NULL OPTIONS (ANNOTATION 'The source table in Data Virtuality Server.')
, IN targetSchema string NOT NULL OPTIONS (ANNOTATION 'A file data source in Data Virtuality Server. The file will be stored in the directory assigned to the file data source.')
, IN targetFile string OPTIONS (ANNOTATION 'The name of the file to store the exported data. If a file with same name exists, it will be overwritten. If omitted, name will be created: sourceSchema_sourceTable.csv.')
, IN delimiter string OPTIONS (ANNOTATION 'When DELIMITER is not specified, by default comma(,) is used as delimiter.')
, IN quote string OPTIONS (ANNOTATION 'Double quotes(") is the default quote character. Use QUOTE to specify a different value. All values will be quoted.')
, IN header boolean OPTIONS (ANNOTATION 'If HEADER is specified, the result contains the header row as the first line - the header line will be present even if there are no rows in a group.')
, IN orderBy string OPTIONS (ANNOTATION 'An ORDER BY-clause may be used to sort the output.')
, IN encoding string OPTIONS (ANNOTATION 'Encoding for the created file. Default is the systems default encoding.')
)
OPTIONS (ANNOTATION 'Exports content of a table into a textfile.')
AS
BEGIN
DECLARE string variables.source_schema = LCASE(REPLACE(sourceSchema, '"', ''));
DECLARE string variables.source_table = LCASE(REPLACE(sourceTable, '"', ''));
DECLARE string variables.target_schema = LCASE(REPLACE(targetSchema, '"', ''));
DECLARE string variables.target_file = targetFile;
DECLARE boolean variables.header = COALESCE(header,FALSE);
DECLARE string variables.order_by = LCASE(REPLACE(orderBy, '"', ''));
DECLARE string variables.delimiter = delimiter;
DECLARE string variables.quote = quote;
DECLARE string variables.encoding = encoding;
DECLARE string variables.columns = '';
DECLARE string variables.export_command = '';
IF
(
(
SELECT a."Name"
FROM "SYS.Schemas" a
WHERE LCASE(a."Name") = variables.source_schema
)
IS NULL
)
BEGIN
ERROR 'Schema ' || variables.source_schema || ' does not exist';
END
IF
(
(
SELECT a."Name"
FROM "SYS.Schemas" a
WHERE LCASE(a."Name") = variables.target_schema
)
IS NULL
)
BEGIN
ERROR 'Schema ' || variables.target_schema || ' does not exist';
END
IF
(
(
SELECT a."Name"
FROM "SYS.Tables" a
WHERE
LCASE(a."Name") = variables.source_table
AND LCASE(a."schemaName") = variables.source_schema
)
IS NULL
)
BEGIN
ERROR 'Table ' || variables.source_table || ' does not exist';
END
IF
(
variables.target_file IS NULL
OR variables.target_file = ''
)
BEGIN
variables.target_file =
(
variables.source_schema
|| '_'
|| variables.source_table
|| '.csv'
)
;
END
LOOP ON
(
SELECT a."Name"
FROM "SYS"."Columns" a
WHERE
LCASE(a."tableName") = variables.source_table
AND LCASE(a."schemaName") = variables.source_schema
ORDER BY a.position
) AS cursor_column_names
BEGIN
variables.columns =
(
variables.columns
|| 'COALESCE("'
|| cursor_column_names."Name"
|| '",'''') AS "'
|| cursor_column_names."Name"
|| '",'
)
;
END
variables.columns = TRIM(TRAILING ',' FROM variables.columns);
variables.export_command =
'EXEC '
|| variables.target_schema
|| '.saveFile( '''
|| variables.target_file
|| ''', ( SELECT TEXTAGG('
|| variables.columns
|| CASE WHEN variables.delimiter IS NOT NULL THEN ' DELIMITER '''
|| variables.delimiter
|| '''' ELSE '' END
|| CASE WHEN variables.quote IS NOT NULL THEN ' QUOTE '''
|| variables.quote
|| '''' ELSE '' END
|| CASE WHEN variables.header = TRUE THEN ' HEADER ' ELSE '' END
|| CASE WHEN variables.encoding IS NOT NULL THEN ' ENCODING "'
|| variables.encoding
|| '"' ELSE '' END
|| CASE WHEN variables.order_by IS NOT NULL THEN ' ORDER BY '
|| variables.order_by ELSE '' END
|| ')'
|| ' FROM '
|| variables.source_schema
|| '.'
|| variables.source_table
|| ') )'
;
EXECUTE IMMEDIATE variables.export_command;
END
;

Example

CALL UTILS.csvExport(
"sourceSchema" => 'test_tables',
"sourceTable" => 'product',
"targetSchema" => 'file',
"targetFile" => 'productsExport.csv'
);;

See Also

CSV Export with Filterを参照して、実際のCSVエクスポートの前に結果セットをフィルタリングする方法を確認してください。

CSV Export with FTP Upload FTPデータソースをターゲットSchemaとして直接使用する方法を学ぶには、次の手順に従います。