套用 SQL 轉換Apply SQL Transformation

本文描述 Azure Machine Learning 表設計工具的模組。This article describes a module of Azure Machine Learning designer.

使用「套用 SQL 轉換」模組,您可以:Using the Apply SQL Transformation module, you can:

  • 建立結果的資料表,並將資料集儲存在可攜式 SQLite 資料庫中。Create tables for results and save the datasets in a portable database.

  • 資料型別,在執行自訂轉換,或建立彙總。Perform custom transformations on data types, or create aggregates.

  • 執行 SQL 查詢陳述式來篩選或改變資料並傳回查詢結果為資料表。Execute SQL query statements to filter or alter data and return the query results as a data table.

重要

此課程模組中使用的 SQL 引擎為 SQLiteThe SQL engine used in this module is SQLite. 如需 SQLite 語法的詳細資訊,請參閱 由 Sqlite 瞭解的 SQLFor more information about SQLite syntax, see SQL as Understood by SQLite. 此模組會將資料增加至 SQLite (在記憶體資料庫中),因此模組執行需要更多的記憶體,而且可能會遇到 Out of memory 錯誤。This module will bump data to SQLite, which is in the memory DB, hence the module execution requires much more memory and may hit an Out of memory error. 請確定您的電腦有足夠的 RAM。Make sure your computer has enough RAM.

如何設定套用 SQL 轉換How to configure Apply SQL Transformation

此模組最多可能需要以三個資料集做為輸入。The module can take up to three datasets as inputs. 當您參考連接到每個輸入埠的資料集時,您必須使用名稱 t1t2t3When you reference the datasets connected to each input port, you must use the names t1, t2, and t3. 這些數字表示輸入連接埠的索引。The table number indicates the index of the input port.

以下是說明如何聯結兩個數據表的範例程式碼。Following is sample code to show how to join two tables. t1 和 t2 是連接到「套用 SQL 轉換」之左方和中間輸入埠的兩個資料集:t1 and t2 are two datasets connected to the left and middle input ports of Apply SQL Transformation:

SELECT t1.*
    , t3.Average_Rating
FROM t1 join
    (SELECT placeID
        , AVG(rating) AS Average_Rating
    FROM t2
    GROUP BY placeID
    ) as t3
on t1.placeID = t3.placeID

其餘的參數是 SQL 查詢,會使用 SQLite 語法。The remaining parameter is a SQL query, which uses the SQLite syntax. 在 [ SQL 腳本 ] 文字方塊中輸入多行時,請使用分號來結束每個語句。When typing multiple lines in the SQL Script text box, use a semi-colon to terminate each statement. 否則,插入換行符號會轉換成空格。Otherwise, line breaks are converted to spaces.

此模組支援 SQLite 語法的所有標準陳述式。This module supports all standard statements of the SQLite syntax. 如需不支援的語句清單,請參閱 技術附注 一節。For a list of unsupported statements, see the Technical Notes section.

技術說明Technical notes

本節包含對常見問題的執行詳細資料、秘訣和解答。This section contains implementation details, tips, and answers to frequently asked questions.

  • 埠1上一律需要輸入。An input is always required on port 1.

  • 對於包含空格或其他特殊字元的資料行識別碼,在參考或子句中的資料行時,一律以方括弧或雙引號括住資料行 SELECT 識別碼 WHEREFor column identifiers that contain a space or other special characters, always enclose the column identifier in square brackets or double quotation marks when referring to the column in the SELECT or WHERE clauses.

不支援的語句Unsupported statements

雖然 SQLite 支援大部分的 ANSI SQL 標準,它不包含許多商業的關聯式資料庫系統所支援的功能。Although SQLite supports much of the ANSI SQL standard, it does not include many features supported by commercial relational database systems. 如需詳細資訊,請參閱 由 SQLite 瞭解的 SQLFor more information, see SQL as Understood by SQLite. 此外,在建立 SQL 語句時,請留意下列限制:Also, be aware of the following restrictions when creating SQL statements:

  • 不過,不像大多數的 SQL 資料庫系統,SQLite 使用動態輸入的值,而非指定類型至資料行。SQLite uses dynamic typing for values, rather than assigning a type to a column as in most relational database systems. 它弱型別,並允許隱含型別轉換。It is weakly typed, and allows implicit type conversion.

  • LEFT OUTER JOIN 是實作為,但不是 RIGHT OUTER JOINFULL OUTER JOINLEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.

  • 您可以使用RENAME TABLEADD COLUMN陳述式與ALTER TABLE命令,但其他子句不支援,包括DROP COLUMNALTER COLUMN,和ADD CONSTRAINTYou can use RENAME TABLE and ADD COLUMN statements with the ALTER TABLE command, but other clauses are not supported, including DROP COLUMN, ALTER COLUMN, and ADD CONSTRAINT.

  • 您可以建立一個檢視內 SQLite,但之後檢視是唯讀。You can create a VIEW within SQLite, but thereafter views are read-only. 無法執行DELETEINSERT,或UPDATE檢視上的陳述式。You cannot execute a DELETE, INSERT, or UPDATE statement on a view. 不過,您可以建立會引發觸發程序,在嘗試DELETEINSERT,或UPDATE檢視上並執行其他作業的觸發程序主體中。However, you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE on a view and perform other operations in the body of the trigger.

除了官方 SQLite 網站上提供的不支援函式清單之外,下列 wiki 還提供其他不支援的功能清單: SQLite-不支援的 SQLIn addition to the list of non-supported functions provided on the official SQLite site, the following wiki provides a list of other unsupported features: SQLite - Unsupported SQL

後續步驟Next steps

請參閱 Azure Machine Learning 的可用模組集See the set of modules available to Azure Machine Learning.