このプロシージャにより、ユーザーは表の内容をCSV 形式でエクスポートできます。
Parameters
Parameter | Description |
---|---|
| Source schema in the Data Virtuality Server; mandatory |
| Source table in the Data Virtuality Server; mandatory |
| File data source in the DataVirtuality Server. The file will be stored in the directory assigned to the file data source; mandatory |
| 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: |
| If not specified otherwise, comma( |
| If not specified otherwise, double quotes("") are used as the quote character by default. All values will be quoted |
| 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 |
| May be used to sort the output |
| 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として直接使用する方法を学ぶには、次の手順に従います。