Implementing Analytics with Greenplum UAP - Getting Started with Greenplum for Big Data Analytics - page 102

'gphdfs://hdfshost:8081/data/filename.txt'
) FORMAT 'TEXT' (DELIMITER '|');
For file-based loading, we can also leverage gpfdist protocol that provides best
performance. Details on gpfdist and its usage are covered in a separate section
in the later part of this chapter.
Some applications of external tables are as follows:
• Execute queries on external data
• Eliminate badly formatted rows using single row error isolation strategy
• Perform ETL load and data unloads
Greenplum Database has readable and writable external tables:
• Readableexternaltables : They are used for loading data and support basic
extraction, transformation, and loading (ETL) tasks for data warehousing.
Greenplum Database segment instances read external table data in parallel
to optimize large load operations. Data in these tables cannot be modified.
• Writable external tables : They are used for unloading data from Greenplum
Database. Writable external tables perform the following:
• Fetch data from database tables into writable external table
• Connect to another database or ETL to load data elsewhere
• Export data into files, named pipes, or trigger other executables
• Interpret output from Greenplum parallel MapReduce process
• Writable external tables allow only INSERT operations
Important points to consider while handling external tables in Greenplum are as fol-
lows:
• When defining an external table, the table and the columns are named just
like any other table.
• Add WEB clause to define a WEB external table.
• We use one of the following protocols to access external table data sources.
A mix of protocols in the CREATE EXTERNAL TABLE statements is not al-
lowed.