SQL 変換の適用

入力データセットに SQLite クエリを実行して、データを変換します

カテゴリ: データ変換/操作

モジュールの概要

この記事では Machine Learning Studio (クラシック) の [ SQL 変換の適用] モジュールを使用して、入力データセットまたはデータセットに対する SQL クエリを指定する方法について説明します。

SQL は、複雑な方法でデータを変更する必要がある場合や、他の環境で使用するためにデータを保持する場合に便利です。 たとえば、 Apply SQL Transformationモジュールを使用すると、次のことができます。

• 結果のテーブルを作成し、データセットを移植可能なデータベースに保存する。

• データ型に対してカスタム変換を実行するか、集計を作成する。

• SQL クエリ ステートメントを実行してデータをフィルター処理または変更し、クエリ結果をデータ テーブルとして返す。

SQLite とは

SQLite は、C プログラミング ライブラリに含まれているパブリック ドメイン リレーショナル データベース管理システムです。 SQLite は、Web ブラウザーでローカル ストレージ用の組み込みデータベースとして一般的に採用されています。

SQLite は元来、サーバーなしのトランザクションをサポートするために、米国海軍で 2000 年に設計されました。 管理システムを持たず、そのため構成や管理を必要としない、自己完結型のデータベース エンジンです。

Apply SQL Transformation (SQL 変換の適用) を構成する方法

このモジュールは、最大 3 つのデータセットを入力として受け取ることができます。 各入力ポートに接続されているデータセットを参照する場合は、t1、t2、t3 の名前を使用する必要があります。 テーブル番号は、入力ポートのインデックスを示しています。

残りのパラメーターは、SQLite 構文を使用する SQL クエリです。 このモジュールでは、SQLite 構文のすべての標準ステートメントがサポートされます。 サポートされていないステートメントの一覧については、「テクニカル ノート」セクションを参照してください。

一般的な構文と使用法

• SQL スクリプト テキスト ボックスに複数の行を入力する場合は、セミコロンを使用して各ステートメントを終了します。 そうしないと、改行はスペースに変換されます。

  たとえば、次のステートメントは互いに対応しています。

  SELECT   
  *   
  from   
  t1;  
  

  SELECT * from t1;  
  

• コメントを追加するには、各行の先頭でを使用 -- するか、を使用 /* */ してテキストを囲みます。

  たとえば、次のステートメントは有効です。

  SELECT * from t1  
  /*WHERE ItemID BETWEEN 1 AND 100*/;  
  

• 予約されたキーワードの名前と列名が重複している場合は、 SQL スクリプトテキストボックス内のテキストに構文の強調表示が適用されます。 混乱を避けるためには、列名を角かっこで囲む必要があります (transact-sql SQL 規則に従う場合)。または、バックティックまたは二重引用符 (ANSI SQL 規約) を使用します。

  たとえば、次のような血液の寄付データセットに対するクエリでは、 Time は有効な列名ですが、予約済みのキーワードでもあります。

  SELECT Recency, Frequency, Monetary, Time, Class  
  FROM t1  
  WHERE Time between 3 and 20;  
  

  クエリをそのまま実行すると、クエリは正しい結果を返す可能性がありますが、データセットによってはエラーが返される場合があります。 ここでは、この問題を回避する方法の例をいくつか示します。

  -- Transact-SQL  
  SELECT [Recency], [Frequency], [Monetary], [Time], [Class]  
  FROM t1  
  WHERE [Time] between 3 and 20;  
  -- ANSI SQL  
  SELECT "Recency", "Frequency", "Monetary", "Time", "Class"  
  FROM t1  
  WHERE `Time` between 3 and 20;  
  

  注意

  構文の強調表示は、引用符または角かっこで囲まれた後もキーワードに残ります。

• SQLite では大文字と小文字が 区別されません。ただし、大文字と小文字が区別され、意味が異なる (glob と glob) ことがあります。

SELECT ステートメント

SELECTステートメントでは、空白や識別子で禁止されているその他の文字を含む列名は、二重引用符、角かっこ、またはバックティック文字 (') で囲む必要があります。

たとえば、このクエリはの t1 Two-Class 虹彩データセットを参照しますが、1つの列名に禁止文字が含まれているため、列名は引用符で囲まれます。

SELECT class, "sepal-length" FROM t1;  

句を WHERE 追加して、データセット内の値をフィルター処理できます。

SELECT class, "sepal-length" FROM t1 WHERE "sepal-length" >5.0;  

SQLite 構文では、キーワードは TOP サポートされていません。これは、SQL で使用されます。 代わりに、キーワードまたは FETCH ステートメントを使用 LIMIT できます。

たとえば、自転車のレンタルデータセットに対してこれらのクエリを比較します。

-- unsupported in SQLite  
SELECT  TOP 100 [dteday] FROM t1 ;  
ORDER BY [dteday] DESC;  
  
-- Returns top 100   
SELECT  [dteday] FROM t1 LIMIT 100 ;  
ORDER BY [dteday] DESC;  
  
-- Returns top 100. Note that FETCH is on a new line.  
SELECT  [dteday] FROM t1 - ;  
FETCH FIRST 100 rows ONLY;  
ORDER BY [dteday] DESC;  

結合

次の例では、t1 に対応する入力ポート上のレストラン評価のデータセットと、t2 に対応する入力ポート上のレストランの特徴のデータセットを使用します。

次のステートメントでは、2 つのテーブルを結合して、指定したレストランの特徴と、各レストランの平均評価を組み合わせたデータセットを作成します。

SELECT DISTINCT(t2.placeid),    
t2.name, t2.city, t2.state, t2.price, t2.alcohol,  
AVG(rating)  AS 'AvgRating'   
FROM t1   
JOIN t2  
ON t1.placeID = t2.placeID  
GROUP BY t2.placeid;  

集計関数

ここでは、SQLite を使用した一般的な SQL 集計関数の基本的な例について説明します。

現在サポートされている集計関数は AVG 、 COUNTMAX 、、、 SUMMIN 、、 TOTAL です。

次のクエリでは、レストラン ID と、レストランの平均評価を含むデータセットを返します。

SELECT DISTINCT placeid,  
AVG(rating) AS ‘AvgRating’,  
FROM t1  
GROUP BY placeid  

文字列の操作

SQLite は、文字列を連結する二重パイプ演算子をサポートします。

次のステートメントは 2 つのテキスト列を連結することによって、新しい列を作成します。

SELECT placeID, name,   
(city || '-' || state) AS 'Target Region',   
FROM t1  

COALESCE と CASE

COALESCE 複数の引数を順番に評価し、NULL として評価されない最初の式の値を返します。

たとえば、マルチクラスの鉄鋼焼きなましのデータセットに対する次のクエリは、相互に排他的な値があると見なされる列の一覧から最初の null 以外のフラグを返します。 フラグが見つからない場合、文字列 “none” が返されます。

SELECT classes, family, [product-type],  
COALESCE(bt,bc,bf,[bw/me],bl, "none") AS TemperType  
FROM t1;  

ステートメントは、 CASE 値をテストし、評価された結果に基づいて新しい値を返す場合に便利です。 SQLite では、ステートメントに対し CASE て次の構文がサポートされています。

• CASE WHEN [condition] THEN [expression] ELSE [expression] END

• CASE [expression] WHEN [value] THEN [expression] ELSE [expression] END

たとえば、以前に " インジケーター値に変換 " モジュールを使用して、true-false 値を含む set 特徴列を作成したとします。 次のクエリでは、複数の特徴列の値を1つの複数の値を持つ列に折りたたみます。

SELECT userID, [smoker-0], [smoker-1],  
CASE  
WHEN [smoker-0]= '1' THEN 'smoker'   
WHEN [smoker-1]= '1' THEN 'nonsmoker'   
ELSE 'unknown'  
END AS newLabel  
FROM t1;  

例

機械学習の実験でこのモジュールを使用する方法の例については、 Azure AI Galleryの次のサンプルを参照してください。

• SQL 変換の適用: レストランの規制、レストランの特徴、およびレストランの顧客のデータセットを使用して、単純な結合、select ステートメント、および集計関数を示します。

テクニカル ノート

このセクションには、実装の詳細、ヒント、よく寄せられる質問への回答が含まれています。

• ポート 1 では、常に入力が必要です。

• 入力データセットに列名がある場合、出力データセット内の列は、入力データセットの列名を使用します。

  入力データセットに列名がない場合、テーブルの列名は自動的に作成されます。このとき、T1COL1、T1COL2、T1COL3 などの命名規則が使用されます。この数字は、入力データセットの各列のインデックスを示します。

• 空白またはその他の特殊文字を含む列識別子の場合、SELECT 句または WHERE 句で列を参照するときに、必ず列識別子を角かっこまたは二重引用符で囲んでください。

サポートされていないステートメント

SQLite では ANSI SQL 標準の多くがサポートされていますが、商用リレーショナル データベース システムでサポートされている多くの機能が含まれていません。 詳細については、「SQLite によって認識される SQL」を参照してください。 また、SQL ステートメントを作成するときは、次の制限事項に注意してください。

• SQLite は、ほとんどのリレーショナル データベース システムのように列に型を割り当てるのではなく、値に対して動的型付けを使用します。 弱い型付けであるため、暗黙的な型変換が可能です。

• LEFT OUTER JOIN は実装されていますが、RIGHT OUTER JOIN または FULL OUTER JOIN は実装されていません。

• RENAME TABLE ステートメントと ADD COLUMN ステートメントは ALTER TABLE コマンドで使用できますが、DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT などの他の句はサポートされていません。

• SQLite 内でビューを作成することはできますが、その後、ビューは読み取り専用になります。 ビューに対して DELETE、INSERT、または UPDATE ステートメントを実行することはできません。 ただし、ビューに対して DELETE、INSERT、または UPDATE を試行したときに起動するトリガーを作成し、そのトリガーの本体で他の操作を実行できます。

SQLite の公式サイトで提供されているサポートされていない関数の一覧に加え、次の Wiki では、サポートされていないその他の機能の一覧が提供されています。SQLite - サポートされていない SQL

想定される入力

モジュールのパラメーター

出力

例外

Studio (クラシック) モジュール固有のエラーの一覧については、「 Machine Learning エラーコード」を参照してください。

API 例外の一覧については、「 Machine Learning REST API のエラーコード」を参照してください。

こちらもご覧ください

操作
データ変換
モジュールの一覧 (アルファベット順)