database - 在 Greenplum (PostgreSQL) 中格式化外部表

Question

我想使用外部表将普通文件加载到 Greenplum 数据库中。我可以为时间戳/日期/时间字段指定输入格式吗？ (如果你知道PostgreSQL的答案，请一并回复)

例如，对于 Oracle，我可以使用 DATE_FORMAT DATE MASK 'YYYYMMDD' 来说明如何解析日期。对于 Netezza，我可以指定 DATESTYLE“YMD”。对于 Greenplum，我找不到答案。我可以将字段描述为 char，然后在加载期间解析它们，但这是一个丑陋的解决方法。

这是我的暂定代码:

CREATE EXTERNAL TABLE MY_TBL (X date, Y time, Z timestamp ) 
LOCATION (
 'gpfdist://host:8001/file1.txt',
 'gpfdist://host:8002/file2.txt'
) FORMAT 'TEXT' (DELIMITER '|' NULL '')

Answer 1

看起来你可以:

SET DATESTYLE = 'YMD';

在从表中SELECT之前。但是，这将影响所有日期的解释，而不仅仅是文件中的日期。如果您在其他地方始终使用明确的 ISO 日期，那会很好，但如果(例如)您还需要在同一查询中接受“D/M/Y”日期文字，则可能会出现问题。

这特定于 GreenPlum 的CREATE EXTERNAL TABLE，不适用于 SQL 标准 SQL/MED 外部数据包装器，如下所示。

---

让我感到惊讶的是 PostgreSQL 本身(它没有这个 CREATE EXTERNAL TABLE 特性)总是接受 ISO 风格的 YYYY-MM-DD 和 YYYYMMDD 日期，与 DATESTYLE 无关。观察:

regress=> SELECT '20121229'::date, '2012-12-29'::date, current_setting('DateStyle');
    date    |    date    | current_setting 
------------+------------+-----------------
 2012-12-29 | 2012-12-29 | ISO, MDY
(1 row)

regress=> SET DateStyle = 'DMY';
SET
regress=> SELECT '20121229'::date, '2012-12-29'::date, current_setting('DateStyle');
    date    |    date    | current_setting 
------------+------------+-----------------
 2012-12-29 | 2012-12-29 | ISO, DMY
(1 row)

...因此，如果 GreenPlum 的行为方式相同，则无需执行任何操作即可从输入文件中正确读取这些 YYYYMMDD 日期。

这是它如何与 PostgreSQL 一起工作 file_fdw SQL/MED foreign data wrapper :

CREATE EXTENSION file_fdw;

COPY (SELECT '20121229', '2012-12-29') TO '/tmp/dates.csv' CSV;

SET DateStyle = 'DMY';

CREATE SERVER csvtest FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE csvtest (
    date1 date,
    date2 date
) SERVER csvtest OPTIONS ( filename '/tmp/dates.csv', format 'csv' );

SELECT * FROM csvtest ;
   date1    |   date2    
------------+------------
 2012-12-29 | 2012-12-29
(1 row)

CSV 文件内容为:

20121229,2012-12-29

因此您可以看到 Pg 将始终接受 CSV 的 ISO 日期，而不管数据样式如何。

如果 GreenPlum 没有，请提交错误。 DateStyle 改变创建后读取外部表的方式的想法很疯狂。