Pentaho Kettle:如何执行“插入...选择”与sql脚本步骤?
我正在发现 Pentaho DI,并且遇到了这个问题:
我想将 csv 文件中的数据插入到自定义数据库中,该数据库不支持“插入表”步骤。所以我想使用 sql 脚本步骤,通过一个请求:
INSERT INTO myTable
SELECT * FROM myInput
我的转换会是这样的:
我不'不知道如何从 csv 中获取所有数据并将其注入“myInput”字段中。
有人可以帮助我吗?
多谢 :)
I am discovering Pentaho DI and i am stuck with this problem :
I want to insert data from a csv file to a custom DB, which does not support the "insert table" step. So i would like to use the sql script step, with one request :
INSERT INTO myTable
SELECT * FROM myInput
And my transformation would like this :
I don't know how to get all my data from the csv to be injected in the "myInput" field.
Could someone help me ?
Thanks a lot :)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
首次编辑 SQL 脚本步骤时,单击“获取字段”按钮。这会将参数(csv 中的字段)加载到左下角的框中。删除不想插入的参数(字段)。
在您的 sql 脚本中编写类似这样的查询,其中问号是按顺序排列的参数。
标记复选框
为每行执行
和作为单个语句执行
。就是这样。如果您还有其他问题,请告诉我,如果您提供示例数据,我将为您制作一个示例 ktr 文件供您查看。When you first edit the SQL Script step, click 'Get fields' button. This is going to load the parameters(fields from your csv) into box on the bottom left corner. Delete the parameters(fields) you don't want to insert.
In your sql script write your query something like this where the question marks are your parameters in order.
Mark the checkboxes
execute for each row
andexecute as a single statement
. That's really about it. Let me know if you have any more questions and if you provide sample data I'll make you a sample ktr file to look at.我认为你走错了路。您应该获得cvs 文件输入 步骤和表输出 步骤。
正如rwilliams所说,在cvs文件输入步骤获取字段;更重要的是,在表输出中有一个数据库字段选项卡。输入字段映射是正确的选择。猜测功能很棒。
另外,当目标连接数据库服务器中不存在目标表时,系统可以生成目标表创建sql语句。
I think you get the wrong way. You should get a cvs file input step and a table output step.
As rwilliams said, In cvs file input step Get fields; the more important, in table output there is a Database Fields tab. Enter field mapping is right choise.The guess function is amazing.
And more, system can generate target table create sql statement when target table not exists in target connection Db server.
使用以下代码
与 CTE 作为
(
从我的输入中选择*
)
选择 *
进入我的表
来自 cte;
Use the following code
with cte as
(
SELECT * FROM myInput
)
select *
into myTable
from cte;