GreenPlum创建外部表示例
来源:互联网 发布:阿里云服务器架设dnf 编辑:程序博客网 时间:2024/05/20 09:43
Example 1—Single Greenplum file server (gpfdist) instance on multiple NIC machine
Creates a readable external table named ext_expenses using the gpfdist protocol. The files are formatted with a pipe ( | ) as the column delimiter.
CREATE EXTERNAL TABLE ext_expenses (
name text,
date date,
amount float4,
category text,
desc1 text )
LOCATION (
'gpfdist://etlhost-1:8081/*',
'gpfdist://etlhost-2:8081/*'
)
FORMAT 'TEXT' (DELIMITER '|');
Example 2—Multiple Greenplum file server (gpfdist) instances
Creates a readable external table named ext_expenses using the gpfdist protocol from all files with the txt extension. The files are formatted with a pipe ( | ) as the column delimiter and an empty space as null.
CREATE EXTERNAL TABLE ext_expenses (
name text,
date date,
amount float4,
category text,
desc1 text )
LOCATION (
'gpfdist://etlhost-1:8081/*.txt',
'gpfdist://etlhost-2:8082/*.txt'
)
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') ;
Example 3—Multiple secure Greenplum file server (gpfdists) instances
Creates a readable external table named ext_expenses using the gpfdists protocol from all files with the txt extension. The files are formatted with a pipe ( | ) as the column delimiter and an empty space as null.
First, run gpfdist with the --ssl option. Then, execute the following command.
CREATE EXTERNAL TABLE ext_expenses (
name text,
date date,
amount float4,
category text,
desc1 text )
LOCATION (
'gpfdists://etlhost-1:8081/*.txt',
'gpfdists://etlhost-2:8082/*.txt'
)
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') ;
Example 4—Single Greenplum file server (gpfdist) instance with error logging
Creates a readable external table named ext_expenses using the gpfdist protocol from all files with the txt extension. The files are formatted with a pipe ( | ) as the column delimiter and an empty space as null.
The external table is accessed in single row error isolation mode. An error table (err_customer) is specified. Any data formatting errors that are found in the input data will be discarded to err_customer, along with a description of the error. err_customer can later be queried in order to see the nature of errors and reload the rejected data after fixing the issues. If the count of badly formatted data rows on any specific segment is greater than five (specified as the SEGMENT REJECT LIMIT value), the entire external table operation will be aborted and no rows will be processed.
CREATE EXTERNAL TABLE ext_expenses (
name text,
date date,
amount float4,
category text,
desc1 text )
LOCATION (
'gpfdist://etlhost-1:8081/*.txt',
'gpfdist://etlhost-2:8082/*.txt'
)
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
LOG ERRORS INTO err_customer SEGMENT REJECT LIMIT 5;
Create the same readable external table definition as above, but with CSV formatted files:
CREATE EXTERNAL TABLE ext_expenses (
name text,
date date,
amount float4,
category text,
desc1 text )
LOCATION (
'gpfdist://etlhost-1:8081/*.txt',
'gpfdist://etlhost-2:8082/*.txt'
)
FORMAT 'CSV' ( DELIMITER ',' )
LOG ERRORS INTO err_customer SEGMENT REJECT LIMIT 5;
Example 5—TEXT Format on a Hadoop Distributed File Server (HDFS)
Creates a readable external table named ext_expenses using the gphdfs protocol. The files are formatted with a pipe ( | ) as the column delimiter.
CREATE EXTERNAL TABLE ext_expenses (
name text,
date date,
amount float4,
category text,
desc1 text )
LOCATION (
'gphdfs://hdfshost-1:8081/data/filename.txt'
)
FORMAT 'TEXT' (DELIMITER '|');
Note: Only one data path is permitted with gphdfs. For examples of reading and writing custom formatted data on a Hadoop Distributed File System.
Example 6—Multiple file protocols in CSV format with header rows
Creates a readable external table named ext_expenses using the file protocol.The wildcard specifications are not the same for all the files. The files are formatted in CSV format and have a header row.
CREATE EXTERNAL TABLE ext_expenses (
name text,
date date,
amount float4,
category text,
desc1 text )
LOCATION (
'file://filehost:5432/data/international/*',
'file://filehost:5432/data/regional/*'
'file://filehost:5432/data/supplement/*.csv'
)
FORMAT 'CSV' (HEADER);
Example 7—Readable Web External Table with Script
Create a readable web external table that executes a script once per segment host:
CREATE EXTERNAL WEB TABLE log_output (linenum int, message text)
EXECUTE '/var/load_scripts/get_log_data.sh' ON HOST
FORMAT 'TEXT' (DELIMITER '|');
Example 8—Writable External Table that Writes to a File
Create a writable external table named sales_out that uses gpfdist to write output data to a file named sales.out. The files are formatted with a pipe (|) as the column delimiter and an empty space as null.
CREATE WRITABLE EXTERNAL TABLE sales_out (LIKE sales)
LOCATION ('gpfdist://etl1:8081/sales.out')
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
DISTRIBUTED BY (txn_id);
Example 9—Writable External Web Table with Script
Create a writable external web table that pipes output data received by the segments to an executable script named to_adreport_etl.sh:
CREATE WRITABLE EXTERNAL WEB TABLE campaign_out
(LIKE campaign)
EXECUTE '/var/unload_scripts/to_adreport_etl.sh'
FORMAT 'TEXT' (DELIMITER '|');
Use the writable external table defined above to unload selected data:
INSERT INTO campaign_out SELECT * FROM campaign WHERE customer_id=123;
Example 10—Readable and Writable External Tables with XML Transformations
Greenplum Database now can read and write XML data to and from external tables with gpfdist. For information on setting up an XML transform, see “Transforming XML Data” on page 178. The following code reads XML data into a table.
CREATE READABLE EXTERNAL TABLE prices_readable (LIKE prices)
LOCATION ('gpfdist://127.0.0.1:8080/data/prices.xml#transform=prices_input')
FORMAT 'text' (delimiter '|')
LOG ERRORS INTO prices_errortable SEGMENT REJECT LIMIT 10;
The following code creates a writable external table that transforms the data in the Greenplum Database to XML.
CREATE WRITABLE EXTERNAL TABLE prices_readable (LIKE prices)
LOCATION ('gpfdist://127.0.0.1:8080/data/prices.xml#transform=prices_input')
FORMAT 'text' (delimiter '|');
0 0
- GreenPlum创建外部表示例
- greenplum创建外部表
- 创建外部链接表示例
- greenplum创建外部表笔记-readable篇
- Greenplum外部表问题解决一例
- Greenplum中创建外部表进行数据导出
- Greenplum(4.3.8.2)使用GPHDFS协议创建外部表访问CDH5.7.0环境的HDFS文件系统
- Greenplum 创建表空间
- Greenplum创建表空间
- 创建mysql表示例
- 在Greenplum外部表中使用HDFS
- GREENPLUM导出多张外部表表结构
- postgresq通过l外部表连接greenplum
- GreenPlum 外部表external table 实战
- GreenPlum 可读写外部表 实战
- greenplum通过hdfs访问外部表
- 创建动态内表示例
- hive里建内部表和外部表示例
- Spring ActionScript入门教程
- Hibernate继承和聚合的实现
- Ext_面板_Ext.Panel .
- JAVA笔记【20131214】
- QTP识别浏览器IE的对象依靠的BHO Manager
- GreenPlum创建外部表示例
- 总监生日
- spring作用域
- iOS 通知机制 Notifications (三)
- Struts2 拦截器总结(内置和新建)[转]
- 【cocos2d-x IOS游戏开发-城市跑酷14】重写触屏[Touch]事件处理机制,响应玩家操作
- MISCONF Redis is configured to save RDB snapshots, but is currently not able to persist on disk.
- Struts2如何返回JSON数据
- Oracle11g安装完成后的一些服务