Netezza数据加载指南：快速掌握nzload外部表

4星 · 超过85%的资源需积分: 10 123 浏览量更新于2024-07-22 收藏 739KB PDF 举报

本指南是关于IBM Netezza 6.0.8的数据加载，提供了详细的信息和实用步骤，专为数据加载过程中的操作者设计。Netezza是一种高度优化的并行数据库系统，适用于大规模数据处理。"nzload external table"是核心功能，用于将外部数据导入到Netezza中。在数据加载组件部分，该文档概述了不同的数据加载方式，包括对不同格式数据的支持，如新引入的十进制分隔符选项，这有助于处理和兼容各种来源的数据。对于外部表，文档详细解释了其概念，包括所需的权限、如何查看和管理外部表信息，以及与日志文件的关联。创建和使用外部表时，需要理解命令语法和临时（transient）外部表的概念，它们允许在特定会话期间使用而无需持久定义。此外，还有明确和隐式模式定义的区别，前者要求用户显式指定数据表的架构，后者则依赖于上下文或默认设置。在数据类型支持方面，指南涵盖了整数、固定点数、浮点数、字符字符串和时间等数据类型的处理，明确了各自的限制和最佳实践。特别强调了在处理时间数据时的注意事项，因为可能涉及到日期和时间的解析和格式化问题。此外，还提供了示例来帮助读者更好地理解和应用这些技术，包括如何通过临时外部表进行数据导出，以及如何在远程环境中使用这种功能。备份和恢复策略也是重要内容，确保数据安全和完整性。这份IBM Netezza Data Loading Guide是一份重要的参考资料，为Netezza用户的日常操作提供了清晰的指导，无论是初次使用者还是经验丰富的管理员都能从中受益。通过遵循文档中的最佳实践，用户可以有效地提升数据加载的效率和准确性。

20525 Rev.4 2-3

About External Tables

To load an external data file into the Netezza appliance as an external table, you can do

either of the following:



Use a FROM clause of a SELECT SQL statement/command, like any normal table.



Use a WHERE clause of an UPDATE or DELETE SQL statement.

To unload an external table into an external data file, use the table as the target table in

any of the following SQL statements:



INSERT SQL



SELECT INTO SQL



CREATE TABLE AS SELECT SQL

All references to columns in the external table can be complex SQL expressions used for

the transformation of external data during a load/unload process. For more information, see

“Restrictions” on page 2-13.

Parsing

For loads, the sequence of rows are parsed one-by-one from the external data file, and con-

verted into internal records of the external table. There could be errors during the parsing of

each row, or each column. For example, there could be errors in identifying the column

value itself, as in the case of a missing delimiter. Or there could be errors during the con-

version from external format to internal records of the external table, such as alphabets

mentioned for an integer column in Text-Delimited format.

Each error is logged in detail in an nzlog file, and bad rows are logged in an nzbad file.

These files help user to identify bad rows in the external data file and correct them for

reloading. Depending on the load options of the external table in use, each bad row would

either cause the row to be skipped, or the entire load to be aborted. Similarly, each bad col-

umn of a bad row could cause the rest of the row to be ignored, or if possible to recover, the

load could continue to parse subsequent columns of the same row.

Note that if there is an error in the project-expression on the external table columns, then

the entire load is aborted and the transaction rolled back. Errors of this nature are not

logged in nzbad or nzlog files, as they are outside of the scope of the external table load

mechanism. Once the processing reaches the normal SQL engine, the external table is

treated as if it is a normal table.

Unlike an external table that has external rows in an ordered sequence, normal user tables

have no implicit row order other than hidden rowid columns. So there is no way for a user

not using rowids to identify the bad row in a SQL engine. In this case, the Netezza system

just returns an error that a particular column caused an error, without identifying the bad

row. It is as if the query was selecting from a normal table and inserting into another nor-

mal table, with some row that caused the error during insertion.

2-4 20525 Rev.4

Netezza Data Loading Guide

Backing Up and Restoring

You can use external tables to back up a table in the system database. While the Netezza

appliance database backup utility, nzbackup, enables you to create backups of the entire

database, the external table backup method allows you to create a backup of a single table,

with the ability to later restore it to the database as needed.



To back up table data using an external table, create external table definitions for each

user table and then use SQL to insert into the external table.



When you restore table data, create a table definition (if it does not exist) and then use

SQL to insert into the table from an external table.

Command Syntax

The CREATE EXTERNAL TABLE command has the following syntax.



To create an external table based on another table:

CREATE EXTERNAL TABLE table_name

SAMEAS table_name

USING external_table_options



To create an external table by defining columns:

CREATE EXTERNAL TABLE table_name

({ column_name type

[ column_constraint [ ... ] ]} [, ... ]

)

[USING external_table_options]

Note: The system permits and maintains PRIMARY KEY, DEFAULT, UNIQUE, and REFER-

ENCES. UNIQUE, PRIMARY KEY and REFERENCES are ignored for external tables. The

system does not support constraint checks and referential integrity. The user must ensure

constraint checks and referential integrity. For more information, see “Column Constraint

Rules for Empty Strings” on page 2-10.

Transient External Tables

Transient external tables (TET) provide a way to define an external table that exists only for

the duration of a single query. Transient external tables have the same capabilities and lim-

itations as normal external tables. A special feature of a TET is that the schema does not

have to be defined when the TET is used to load data into a table or when the TET is cre-

ated as the target of a SELECT statement.

Syntax

The following is the syntax for a TET:

INSERT INTO <table> SELECT <column_list | *>

FROM EXTERNAL 'filename' [(schema_definition)]

[USING (external_table_options)];

CREATE EXTERNAL TABLE 'filename' [USING (external_table_options)]

AS select_statement;

20525 Rev.4 2-5

Transient External Tables

SELECT <column_list | *> FROM EXTERNAL 'filename' (schema_definition)

[USING (external_table_options)];

Explicit Schema Definition

The schema of a transient external table can be explicitly defined in a query. When defined

this way, the schema definition is the same as is used when defining a schema using CRE-

ATE TABLE.

SELECT x, y, NVL(dt, current_date) AS dt FROM EXTERNAL '/tmp/test.txt'

( x integer, y numeric(18,4), dt date ) USING (DELIM ',');

The explicit schema definition feature can be used to specify fixed length formats.

SELECT * FROM EXTERNAL '/tmp/fixed.txt' ( x integer, y numeric(18,4),

dt date ) USING (FORMAT 'fixed' LAYOUT (bytes 4, bytes 20, bytes 10));

The SAMEAS keyword can also be used to specify that the schema of the external table is

identical to some other table that currently exists in the database.

SELECT * FROM EXTERNAL '/tmp/test.txt' SAMEAS test_table

USING (DELIM ',');

Implicit Schema Definition

If the schema is not explicitly defined, the schema for a transient external table is deter-

mined based on the query being executed. When a TET is used as a data source for an

INSERT statement, the external table will take on the schema of the target table.

The external table in this INSERT statement takes on the schema of the target table. The

columns in the external data file must be in the same order as the target table, and every

column in the target table must also exist in the external table data file.

INSERT INTO target SELECT * FROM external '/tmp/data.txt'

USING (DELIM '|');

Exporting Data Using Transient External Tables

A transient external table can also be used to export data out of the database. In this case

the schema of the external table is based on the query being executed.

Example:

CREATE EXTERNAL TABLE '/tmp/export.csv' USING (DELIM ',') AS

SELECT foo.x, bar.y, bar.dt FROM foo, bar WHERE foo.x = bar.x;

Remote Transient External Tables

A session connected to Netezza using ODBC, JDBC, or OLE DB from a client system can

import and export data using a remote transient external table, which is defined by using

the REMOTESOURCE option in the USING clause.

For example, the following SQL statement loads data from a file on a Windows system into

a TEMP table on Netezza, using an ODBC connection.

CREATE TEMP TABLE mydata AS SELECT cust_id, upper(cust_name) as name

from external 'c:\customer\data.csv' (cust_id integer, cust_name

varchar(100)) USING (DELIM ',' REMOTESOURCE 'ODBC');

剩余89页未读，继续阅读

伴生伴熟

粉丝: 43
资源: 8

Netezza数据加载指南：快速掌握nzload外部表

Netezza学习指导

Netezza 数据库入门

Netezza用户指南

Netezza_stored_procedures_guide

Netezza_system_admin_guide.pdf

Netezza_getting_started_tips.pdf 英文版

Spoon_User_Guide_3_0

The Netezza Data Appliance Architecture.pdf

Netezza overview

Netezza调优

最新资源