CSVファイルを外部テーブルとして利用する方法

公開日: 公開日：2016年05月02日（月）
更新日: 更新日：2019年02月01日（金）
サブカテゴリー: 基本操作

タグ: #手順; #制限事項

はじめに

Verticaには、データベース上にテーブルとして存在しているかのようにCSVファイルを外部テーブルとして利用する機能があります。

外部テーブル機能を利用すると、CSVファイルとデータベース内のテーブルのデータを結合して結果を取得するなど、通常のSQLと同様の処理を行うことができます。また、ライセンス対象となるデータサイズ外として使えるというメリットもあります。

利用手順

外部テーブルとして利用するためには以下の手順で行います。

1.CSVファイルを任意のディレクトリに配置、権限を付与する

はじめに対象のCSVファイルを配置し、権限を付与します。

$ chown dbadmin:verticadba /tmp/lineorder.tbl
$ ls -ltr /tmp/lineorder.tbl
-rwxr-xr-x 1 dbadmin verticadba 594296771 Apr 26 20:36 /tmp/lineorder.tbl

2.外部テーブル用のテーブルを作成する

外部テーブル作成の基本的な構文は以下の通りです。

CREATE EXTERNAL TABLE [schema.]table-name AS COPY
( { column-as-expression | column } )
FROM ‘pathToData’ ‘option’

実行例は以下のとおりです。

■外部テーブルの作成

$ vsql -w <password>
CREATE EXTERNAL TABLE ssbm.lineorder (
  lo_orderkey numeric,
  lo_linenumber integer,
  lo_custkey  numeric,
  lo_partkey  integer,
  lo_suppkey  numeric,
  lo_orderdate integer,
  lo_orderpriority char(15),
  lo_shippriority char(1),
  lo_quantity numeric,
  lo_extendedprice numeric,
  lo_ordertotalprice numeric,
  lo_discount numeric,
  lo_revenue numeric,
  lo_supplycost numeric,
  lo_tax numeric,
  lo_commit_date integer,
  lo_shipmode char(10)
)
AS COPY FROM '/tmp/lineorder.tbl' DELIMITER '|'
;
CREATE TABLE
Time: First fetch (0 rows): 13.033 ms. All rows formatted: 13.051 ms

上記2ステップを踏まえることで、任意のSQLを実行してCSVファイルのデータを読み込むことができます。
また、作成した外部テーブルは通常のテーブルと同様に定義情報を確認できます。
外部テーブルの場合には、TABLESシステムテーブルの「TABLE_DEFINITION」列にCOPY句が出力されます。

■外部テーブルの確認
dbadmin=> \d lineorder
                                             List of Fields by Tables
 Schema |   Table   |       Column       |      Type      | Size | Default | Not Null | Primary Key | Foreign Key
--------+-----------+--------------------+----------------+------+---------+----------+-------------+-------------
 ssbm   | lineorder | lo_orderkey        | numeric(37,15) |   16 |         | f        | f           |
 ssbm   | lineorder | lo_linenumber      | int            |    8 |         | f        | f           |
 ssbm   | lineorder | lo_custkey         | numeric(37,15) |   16 |         | f        | f           |
 ssbm   | lineorder | lo_partkey         | int            |    8 |         | f        | f           |
 ssbm   | lineorder | lo_suppkey         | numeric(37,15) |   16 |         | f        | f           |
 ssbm   | lineorder | lo_orderdate       | int            |    8 |         | f        | f           |
 ssbm   | lineorder | lo_orderpriority   | char(15)       |   15 |         | f        | f           |
 ssbm   | lineorder | lo_shippriority    | char(1)        |    1 |         | f        | f           |
 ssbm   | lineorder | lo_quantity        | numeric(37,15) |   16 |         | f        | f           |
 ssbm   | lineorder | lo_extendedprice   | numeric(37,15) |   16 |         | f        | f           |
 ssbm   | lineorder | lo_ordertotalprice | numeric(37,15) |   16 |         | f        | f           |
 ssbm   | lineorder | lo_discount        | numeric(37,15) |   16 |         | f        | f           |
 ssbm   | lineorder | lo_revenue         | numeric(37,15) |   16 |         | f        | f           |
 ssbm   | lineorder | lo_supplycost      | numeric(37,15) |   16 |         | f        | f           |
 ssbm   | lineorder | lo_tax             | numeric(37,15) |   16 |         | f        | f           |
 ssbm   | lineorder | lo_commit_date     | int            |    8 |         | f        | f           |
 ssbm   | lineorder | lo_shipmode        | char(10)       |   10 |         | f        | f           |
(17 rows)


dbadmin=> select table_schema,table_name,table_definition from tables where table_name = 'lineorder';
 table_schema | table_name |               table_definition
--------------+------------+-----------------------------------------------
 ssbm         | lineorder  | COPY FROM '/tmp/lineorder.tbl' DELIMITER '|'

(1 row)

SQL実行例

SQLの実行例を以下に記載します。

■全件カウントの実行例

dbadmin=> select count(*) from ssbm.lineorder;
  count
---------
 6001171
(1 row)

Time: First fetch (1 row): 22945.080 ms. All rows formatted: 22945.140 ms

■任意のSELECT文の実行例

select sum(lo_extendedprice*lo_discount) as revenue
from ssbm.lineorder,ssbm.date1
where lo_orderdate = d_datekey
  and d_year = 1993
  and lo_discount between 1 and 3
  and lo_quantity < 25;
                   revenue
---------------------------------------------
 446268068091.000000000000000000000000000000
(1 row)

Time: First fetch (1 row): 23528.306 ms. All rows formatted: 23528.391 ms

通常のテーブルに対する操作と比べるとパフォーマンスは劣りますが、同様の操作を行うことができることをご理解いただけたかと思います。

制限事項や注意点

外部テーブル機能はCSVファイルを読み込んで処理を実行するため、Vertica本来の高速処理の恩恵を受けることができません。また、以下の様な制限事項や注意事項がある点をご確認ください。

ファイルのアクセス権を付与する

OS上に存在するファイルのため、適切なアクセス権を付与しておく必要があります。

更新処理ができない

外部ファイルとして存在するため、テーブルに対してINSERT / UPDATE / DELETE / TRUNCATEを行うことはできません。

プロジェクションを作成できない

外部テーブルに対してプロジェクションを作成することはできません。

ファイルアクセスに伴うI/O

毎回ファイルへのアクセスが発生すること、キャッシュも効かないため頻繁に参照する必要があるデータ、特に件数の多いファイルの読み込みには向きません。

参考情報

【マニュアル】
CREATE EXTERNAL TABLE AS COPY
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATEEXTERNALTABLEASCOPY.htm

検証バージョンについて

この記事の内容はVertica 9.1で確認しています。

Analytics Database