설명
이 Step은 데이터베이스로부터 데이터를 읽어오는데 사용된다. 데이터베이스 커넥션과 SQL문을 이용하게 되며, 기본 SQL구문은 자동으로 생성된다.
옵션
Option |
Description |
|---|---|
Step Name |
Step의 고유 이름. Transformation 내에서 유일한 이름이어야 한다. |
Connection |
데이터베이스 접속 정보 |
SQL |
데이터베이스로부터 읽어올 데이터를 위한 SQL 구문이다. "Get SQL select statement..." 버튼을 클릭하여 데이터베이스 테이블 브라우징을 통해 간단한 Select SQL구문을 자동으로 생성할 수 있다. |
Enable lazy conversion |
이 옵션을 활성화하면 불필요한 데이터 타입 변환을 하지 않게 되며, 이는 성능 향상에 영향을 줄 수 있다. |
Replace variables in script? |
스크립트 내에서 변수 치환을 활성화; this feature was provided to allow you to test with or without performing variable substitutions. |
Insert data from step |
Specify the input step name where Pentaho? an expect information to come from. This information can then be inserted into the SQL statement. The locators where Pentaho? inserts information is indicated by ? (question marks). |
Execute for each row? |
Enable to perform the data insert for each individual row. |
Limit size |
데이터베이스로부터 읽어올 라인의 최대 크기를 설정. zero(0)은 모든 행 허용을 의미한다. |
예제
아래는 간단한 SQL 문 예제이다.
SELECT * FROM customers WHERE changed_date BETWEEN ? AND ?
This statement needs two dates that are read on the Insert data from step.Note: The dates can be provided using the Get System Info step type. For example, if you want to read all customers that have had their data changed yesterday, you may get the range for yesterday and read the customer data.
Preview
Preview allows you to preview the step. This is accomplished by preview of a new transformation with two steps: this one and a
Dummy step. To see a detailed log of the execution, click Logs in the Preview window.
FAQ
Variables are not getting substituted
Q: I'm feeding parameters via a input hop to a table input, but they're not being replaced properly. My input row contains e.g. the fieldname "input_param". The following is my query in Table input step:
SELECT VID FROM CO_VEHICLES; WHERE PONO = $
;
the input_param does not get replaced. How to do this?
A:The reason it fails is that there's a difference between variables and field substitution. The above syntax would work if "input_param" would be a variable. However to use the field values of the incoming rows as arguments you need to use ? Parameters. The query would need to become:
SELECT VID FROM CO_VEHICLES; WHERE PONO = ?;
These parameter markers are filled in positionally.
Parameters are working, but not as part of a tablename
Q: I'm using parameters in a table input step. This works when I use the parameter in a where clause as:
SELECT VID FROM CO_VEHICLES; WHERE POID = ?;
If 1 argument would be passed to the table input step from the previous step. However when I use the parameter marker as part of a tablename as:
SELECT VID FROM CO_VEHICLES_?;
it fails. The intention of the above query would be e.g. to access multiple tables CO_VEHICLES_1, CO_VEHICLES_2, ... where the number would be passed as a parameter. This always fails, why?
A:The reason it fails is that JDBC does not allow parameter markers in a table. The reason for this is simple: the tablename determines the columns you can get retrieve, the access rights, ... These things are checked once for parametrized SQL statements and afterwards only the parameters are filled in to execute the query.
If parameter markers would be allowed in tablenames the SQL statement would have to be prepared every time, hence parameter markers in tablenames are not supported.
There is a way around it: you can use (kettle) variables as part of a tablename as in:
SELECT VID FROM CO_VEHICLES_$
;
What PDI does for these queries (if you enable "variable replacement" in the table input step) is:
- Replace the variables in the SQL statements;
- Bind the parameters per execution;
This way JDBC is not aware of the changing tablename (because it will be filled in by PDI before the SQL is sent over to the database), it will however result in different SQL statements being prepared by the database. There's also another section in is document on the setting of variables.