kettle中實現動態SQL查詢，kettle實現動態SQL

kettle中實現動態SQL查詢，kettle實現動態SQL
kettle中實現動態SQL查詢

 

      在ETL項目中，通常有根據運行時輸入參數去執行一些SQL語句，如查詢資料。本文通過kettle中的表輸入（“table input”）步驟來說明動態查詢、參數查詢。範例程式碼使用記憶體資料庫（H2），下載就可以直接運行，通過樣本學習更輕鬆。

 

SQL查詢語句中預留位置綁定欄位值

      第一個接近動態語句的是大家熟悉的從SQL代碼中執行，開始寫一個SQL查詢，包含一些預留位置，然後綁定值到預留位置，使之成為一個有效查詢並執行。根據需要可以綁定多個值並迴圈執行。本樣本的名稱為placeholders.ktr檔案。

      在樣本中，首先建立presidents表並填入資料（關於美國總統的內容），代碼如下，欄位分別為：名稱、州、政黨、職業、畢業院校、任職日期、離職日期。

CREATE TABLE presidents(

    name VARCHAR(255),

    state VARCHAR(255),

    party VARCHAR(64),

    occupation VARCHAR(64),

    college VARCHAR(64),

    took_office DATE,

    left_office DATE

   

);

下面的查詢語句用問號預留位置，當開始日期（第一個？號）和結束日期（第二個？號）綁定到SQL的問號預留位置，在查詢入職日期在一定期間的總統資訊：

 

SELECTname,took_office FROM presidents WHEREtook_officeBETWEEN? AND?

      樣本中，首先使用產生行步驟（“Generdate Rows”）產生一行帶有兩個欄位的記錄，分別按順序代替表輸入SQL語句中的預留位置。實際情境中，通常使用動態處理結果產生期望值代替產生行步驟。

     

      接下來是表輸入步驟，其中配置SQL查詢語句，包含問號預留位置，通過在“Insert Data Step”的下拉框中選擇前一步驟，來替換問號的值。

 

通過傳輸不同的值多次執行查詢

      如果你想迴圈執行查詢，使用不同值替換預留位置；就需要佔位符生產步驟產生多行資料，並把表輸入的選項“Execute for each row”選中。 本樣本檔案名稱為placeholders_in_loop.ktr。

 

預留位置的局限性

      雖然通過給預留位置綁定值查詢非常有效，但也有一些情境不能使用，下面一些SQL不能使用預留位置。這些樣本都非常通用，但是不能使用預留位置。

不能用預留位置代替表名詞，否則查詢將不執行。

SELECT some_fieldFROM ?

不能使用預留位置代替查詢的欄位名稱，下面的查詢可以成功綁定參數，但只是作為一個常量，而不是欄位的名稱。

SELECT ? asmy_field FROM table

      不能使用預留位置綁定逗號分隔的多個清單項目值；如果你綁定 “1,2,3″ 給下面的查詢語句，將得到意外的結果。

SELECT * FROM testWHERE id IN (?)

你期望得到的結果是：

SELECT * FROM testWHERE id IN ("1,2,3")

但是啟動並執行結果卻是這樣，傳輸一個字串，卻得到三個值，而實際情況完全不確定有幾個值傳輸進來。

SELECT * FROM testWHERE id IN (1,2,3)

 

為瞭解決這些情境的問題，需要使用kettle的變數動態構造查詢文本，下面詳細說明。

 

SQL查詢中使用kettle變數

      表輸入步驟支援替換查詢中的變數或參數，假設有一系列結構完全相關的表，分別是: mammals, birds, insects(動物、鳥、昆蟲)，可以使用kettle變數作為表的名稱。假設我們有一個變數，名稱為：ANIMALS_TABLE,賦值為birds，我們設定“Replace Vaiables”選項選中。如果我們寫下面的查詢：

SELECT name,population FROM ${ANIMALS_TABLE}

在執行一定被成功的替換成：

SELECT name,population FROM birds

如果設定變數的值為“mammals”或“insects”，則將動態查詢不同的表。當預留位置不能勝任是，使用變數技術可以協助我們解決。樣本的名稱為variables.ktr，運行時不要忘了給parameter（具名引數）賦值進行測試。

 

變數和預留位置一起使用

      如果有必要，我們可以混合這兩種技術；本樣本中使用變數作為表名詞，同時使用預留位置作為前面步驟的輸入值。樣本檔案variables_and_placeholders.ktr。

    SELECT name, population FROM ${ANIMALS_TABLE}WHERE population > ?

樣本下載

      可以在這裡下載樣本檔案。所有樣本都kettle5.1版中測試通過，而且測試資料是用H2記憶體資料庫，下載後可以直接運行，非常容易，希望你學習順利。

kettle40 執行sql指令碼 怎在其他任務之前？

在轉換裡面各個【步驟】是並發執行的，資料流是按照順序執行的。
 
問sql中怎實現欄位的動態查詢？

CREATE TABLE #test ( id INT, start_date DATE, end_date DATE);INSERT INTO #test VALUES(1, '2013-01-01','2013-01-31');INSERT INTO #test VALUES(1, '2013-03-01','2013-03-07');INSERT INTO #test VALUES(2, '2013-01-01','2013-03-07');INSERT INTO #test VALUES(2, '2013-02-01','2013-02-07');INSERT INTO #test VALUES(2, '2013-03-01','2013-03-07');INSERT INTO #test VALUES(2, '2013-04-01','2013-04-07');-- 這裡假設 欄位1是 id-- 欄位2不確定,需要根據欄位1返回的記錄再具體確定每條記錄所對應的欄位2。-- 假如欄位1= 1， 則返回 start_date， 否則返回 end_dateSELECT id AS [欄位1], CASE WHEN id = 1 THEN start_date ELSE end_date END AS [欄位2]FROM #test;欄位1 欄位2----------- ---------------- 1 2013-01-01 1 2013-03-01 2 2013-03-07 2 2013-02-07 2 2013-03-07 2 2013-04-07(6 行受影響)