より柔軟な方法で SAP SQL Anywhere へデータをロードする - パート 1 (過去のブログ記事より)

[Sawa_Ito]

このページは、以下の英語ページの抄訳です。最新の情報については、英語ページを参照してください。

 

 

 

この記事のオリジナルは、Glenn Paulley が sybase.com に 2009 年 7 月に掲載したものです。その中で、Glenn は SQL Anywhere へのデータロードの方法として OPENSTRING 句について語っています。

 

 

 

 

フラットファイルのコンテンツを SQL Anywhere サーバーに、あるいは逆に SQL Anywhere サーバーから転送することが必要な場合があります。
Version 11より前のバージョンでは、フラットファイルからのインポートは、以下の方法が可能でした（コードを書いて実行するのではなく）。

 

• LOAD TABLE 文を使用する;


• DBISQL ツールから INPUT 文を使用する;


• xp_read_file() プロシージャーを使用してロードする; または


• リモートデータアクセスサービスを使用して、フラットファイルに対してプロキシーテーブルを構築する

 

上のソリューションそれぞれに、プラス面とマイナス面があります。
最初の 2 つのオプションでは、データをロードするためのテーブルの作成が必要になります。LOAD TABLE は、INPUTよりも高速です。しかし、欠点が 2 つあります。まず、サーバーマシンから直接ファイルにアクセス可能である必要があります。そして、挿入される個々の行がトランザクションログで記録されないようにする必要があります（複雑なリカバリ）。
3番目のオプションもまた、サーバーマシンに対してローカルにある必要があります。残念ながら、xp_read_file() は、パーシングが必要になる曖昧な値を作成してしまいます。
4番目のオプションであるプロキシーテーブルは、外部サーバー、EXTERNLOGIN オブジェクトなどを作成する必要があり、これを使うのはさらに面倒です。

 

Version 11 では、フラットファイルとのインタラクションをより柔軟に処理できるようになりました。このブログ記事では、 OPENSTRINGで提供される機能について説明します。そして、次の記事で、追加のオプションについて説明します。

 

 

OPENSTRING

 

これらの新機能を説明するにあたり、StatCounter で生成された CSV アウトプットファイルをサーバーにアップロードします。このファイルの最初の数行については以下のとおりです ( StatCounter のアカウントを1月20日に登録しました )。

 

1. Day,Date,Page Loads,Unique Visitors,First Time Visitors,Returning Visitors


2. Tuesday,20th January 2009,"83","48","46","2"


3. Wednesday,21st January 2009,"163","108","102","6"


4. Thursday,22nd January 2009,"127","105","99","6"


5. Friday,23rd January 2009,"126","91","87","4"


6. Saturday,24th January 2009,"42","37","35","2"


7. Sunday,25th January 2009,"52","38","36","2"


8. Monday,26th January 2009,"171","133","119","14"


9. Tuesday,27th January 2009,"157","110","101","9"

 

プロキシーテーブル、LOAD TABLE、あるいは INPUT INTO を使用するのではなく、テーブルの作成は作成しないことにし、シンプルに OPENSTRING を使用して、SELECT 文ではフラットファイルを直接参照することにします。

 

OPENSTRING は、インプットパラメーターがフラットファイルか（列）変数のどちらかであるテーブル表現です。 特定された場合には、サーバーはそのファイルのインプットをパースし、OPENSTRING 表現の句内で特定するスキーマをマッチさせながら、バーチャルテーブルの行を構築します。テーブル表現の SQL 文法は以下のとおりです。

 

1. <openstring-expression> ::= OPENSTRING ( { FILE | VALUE } <string-expression> ) WITH ( <rowset-schema> ) [ OPTION ( <scan-option> ...  ) ]


2. <rowset-schema> ::= <column-schema-list> | TABLE [owner.]table-name [ ( column-list ) ]


3. <column-schema-list> ::= { <column-name> <user-or-base-type> | FILLER( ) } [ , ... ]


4. <column-list> ::= { <column-name> | FILLER( ) } [ , ... ]


5. <scan-option> ::= BYTE ORDER MARK { ON | OFF }


6.         | COMMENTS INTRODUCED BY <comment-prefix>


7.         | DELIMITED BY <string>


8.         | ENCODING <encoding>


9.         | ESCAPE CHARACTER <character>


10.         | ESCAPES { ON | OFF }


11.         | FORMAT { TEXT | BCP }


12.         | HEXADECIMAL { ON | OFF }


13.         | QUOTE <string>


14.         | QUOTES { ON | OFF }


15.         | ROW DELIMITED BY <string>


16.         | SKIP integer


17.         | STRIP { ON | OFF | LTRIM | RTRIM | BOTH }

 

OPENSTRING のオプションは、LOAD TABLE で可能なそれらにマッチすることに注意してください。このケースでは、Excel-CSV ファイルは、カンマで定義されるフィールドを使用します。また、最初の行には実際の値の処理では無視されなければならない属性名が含まれます。
このインプットファイルから直接結果セットを作成する SQL 文は以下のとおりです。

 

1. SELECT stat_weekday,


2.       CAST ( ( REGEXP_SUBSTR( str_stat_date, '[0-9]+(?=(st|nd|th|rd|ST|ND|TH|RD )\s.*)' )


3.                     || REGEXP_SUBSTR( str_stat_date, '(?<=[0-9]+(st|nd|th|rd|ST|ND|TH|RD)).*' ) ) AS DATE ) as stat_date,


4.       page_loads, unique_visitors, first_time_visitors, returning_visitors


5. FROM OPENSTRING( VALUE READ_CLIENT_FILE ('c:\gpaulley\blog\summary-6July2009.csv' ) )


6.     WITH( stat_weekday char(10), str_stat_date char(30), page_loads int, unique_visitors int, first_time_visitors int, returning_visitors int)


7.         OPTION( SKIP 1 DELIMITED BY ',' ) AS summary

 

上の文のポイントをいくつか挙げます:

 

• OPENSTRING へのパラメータは、VALUE または FILE のどちらかです。もし FILE が特定されている場合には、そのフラットファイルは、サーバーマシンに対してローカルになければなりません; このケースでは、このファイルは私のコンピューターにあります。
  Version 11 では、READ_CLIENT_FILE 機能をサポートしています。これは、SQL Anywhere の CMDSEQ ワイヤプロトコルを使用して、クライアントマシンから、ファイルのコンテンツをフェッチするものです。READ_CLIENT_FILE は、OPENSTRING が続いて行を作成するためにパースする内部文字列を作成します。


• WITH 句は、ファイルのスキーマを特定します。ファイル内の文字列からのサーバーデータ型へのコンバージョンは、自動的に実行されます。しかしながら、StatCounter によって生成された CSV ファイルは、年日付値 (例えば '21st January 2009')を含んでいたため、日の値は、文字列としてパースされます。


• SQL Anywhere がDATEへコンバートすることが可能な修正された文字列を生成するために、REGEXP_SUBSTR 機能を使います。
  これは、version 11 のもう１つの新機能で、SQL Anywhere の正規表現のサポートに伴って実装されました。
  ここでは、REGEXP_SUBSTR の最初の使用では、肯定の先読みゼロ幅アサーションを使用して、省略された、数値的な、月の特定の日付を返します。REGEXP_SUBSTRの 2 つめのインスタンスは、似ていますが、肯定の先読みゼロ幅アサーションを使用して、文字列の残りを返します。文字列を連結させた場合には、この2つの機能は、 '21st January 2009' を '21 January 2009' にコンバートします。サーバーは、DATE_ORDER 接続オプションによって ('DMY') を適切に選択し、自動的にこのコンバージョンを扱うことが可能です。


• READ_CLIENT_FILE を使用するには、以下の 2 つが必要になります:
  
  
  
  • データベースは、ALLOW_READ_CLIENT_FILE オプションを有効にして、クライアントファイルアクセスを有効にする必要があります。このオプションは、DBA 権限を持つユーザーのみ設定可能です。
  
  
  • READ_CLIENT_FILEを実行するユーザーは、READCLIENTFILE 権限が必要です。
  
  
  
  

 

このクエリのスクリーンショットと結果セットは以下のとおりです。



 

OPENSTRINGは、 - INSERT と MERGEを含む - どの DML 文でも現れるテーブル表現です。また、そのクエリのスコープ内のどの列変数をも参照することが可能です。
さらに、OPENSTRINGをビュー内で有効利用することができます。
ビュー定義が確立されると、ビューからフラットファイルを使用しているプロシージャーまたはアプリケーションがシームレスにアップデート DML 文をそのビューに対して発行できるように、INSTEAD OF トリガーを作成することが可能です。

 

 

 

===

 

SAP SQL Anywhere に関する詳細情報は、SAP SQL Anywhere Communityページを参照してください。

 

SQL Anywhere に関してはまずはこちらをご参照ください。無期限でご利用いただける無償の Developers Edition もこちらからダウンロードが可能です。

 

SQL Anywhere に関して技術的な質問のある方はコミュニティに登録し、
「Ask a Question」機能をご利用ください。

Language には「Japanese」、
Primary Tag には「SAP SQL Anywhere」を選択
してください。

不具合につきましては、サポート契約者様専用の問い合わせ方法にてお問い合わせください。

 

======================
ご購入に関するお問い合わせ

こちらよりお問い合わせください。