Kettle 连 Salesforce 拉数据

已于 2023-02-21 10:11:36 修改
阅读量2.6k 收藏
点赞数
分类专栏： SSIS and Kettle 文章标签： 数据库
于 2018-01-02 18:07:37 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/rav009/article/details/78954312
版权

获取安全Token后, 在kettle里设置的密码是 password+Token 比如密码是123, token是789, 那就输入123789

Salesforce里的Entity与普通数据库的表不一样, 不是每一列都可以filter, 一般只有日期或数字等类型的字段支持filter, 具体可以查阅Salesforce SOQL的文档来得知.

开发过程中还遇到一个Kettle的Spoon突然打不开的问题, 而且没有任何报错信息, 后来发现是JVM的问题. Spoon调用javaw, javaw 没有任何输出的界面, rootcause是JVM不能获取足够的内存资源, 更改了"-Xmx3200m" 参数降低了内存申请的大小后 解决了问题, 或者装个64位的jdk彻底解决 

salesforce 的时间函数

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

Date Literals

A fieldExpression can use a date literal to compare a range of values to the value in a date or dateTime field. Each literal is a range of time beginning with midnight (00:00:00). To find a value within the range, use =. To find values on either side of the range, use > or <. The following table shows the available list of date literals, the ranges they represent, and examples.

Date LiteralRangeExample
YESTERDAYStarts 00:00:00 the day before and continues for 24 hours.SELECT Id FROM Account WHERE CreatedDate = YESTERDAY
TODAYStarts 00:00:00 of the current day and continues for 24 hours.SELECT Id FROM Account WHERE CreatedDate > TODAY
TOMORROWStarts 00:00:00 after the current day and continues for 24 hours.SELECT Id FROM Opportunity WHERE CloseDate = TOMORROW
LAST_WEEKStarts 00:00:00 on the first day of the week before the most recent first day of the week and continues for seven full days. Your locale determines the first day of the week.SELECT Id FROM Account WHERE CreatedDate > LAST_WEEK
THIS_WEEKStarts 00:00:00 on the most recent first day of the week before the current day and continues for seven full days. Your locale determines the first day of the week.SELECT Id FROM Account WHERE CreatedDate < THIS_WEEK
NEXT_WEEKStarts 00:00:00 on the most recent first day of the week after the current day and continues for seven full days. Your locale determines the first day of the week.SELECT Id FROM Opportunity WHERE CloseDate = NEXT_WEEK
LAST_MONTHStarts 00:00:00 on the first day of the month before the current day and continues for all the days of that month.SELECT Id FROM Opportunity WHERE CloseDate > LAST_MONTH
THIS_MONTHStarts 00:00:00 on the first day of the month that the current day is in and continues for all the days of that month.SELECT Id FROM Account WHERE CreatedDate < THIS_MONTH
NEXT_MONTHStarts 00:00:00 on the first day of the month after the month that the current day is in and continues for all the days of that month.SELECT Id FROM Opportunity WHERE CloseDate = NEXT_MONTH
LAST_90_DAYSStarts 00:00:00 of the current day and continues for the past 90 days.SELECT Id FROM Account WHERE CreatedDate = LAST_90_DAYS
NEXT_90_DAYSStarts 00:00:00 of the current day and continues for the next 90 days.SELECT Id FROM Opportunity WHERE CloseDate > NEXT_90_DAYS
LAST_N_DAYS:nFor the number n provided, starts 00:00:00 of the current day and continues for the past n days.SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:365
NEXT_N_DAYS:nFor the number n provided, starts 00:00:00 of the current day and continues for the next n days.SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_DAYS:15
NEXT_N_WEEKS:nFor the number n provided, starts 00:00:00 of the first day of the next week and continues for the next n weeks.SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_WEEKS:4
LAST_N_WEEKS:nFor the number n provided, starts 00:00:00 of the last day of the previous week and continues for the past n weeks.SELECT Id FROM Account WHERE CreatedDate = LAST_N_WEEKS:52
NEXT_N_MONTHS:nFor the number n provided, starts 00:00:00 of the first day of the next month and continues for the next n months.SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_MONTHS:2
LAST_N_MONTHS:nFor the number n provided, starts 00:00:00 of the last day of the previous month and continues for the past n months.SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12
THIS_QUARTERStarts 00:00:00 of the current quarter and continues to the end of the current quarter.SELECT Id FROM Account WHERE CreatedDate = THIS_QUARTER
LAST_QUARTERStarts 00:00:00 of the previous quarter and continues to the end of that quarter.SELECT Id FROM Account WHERE CreatedDate > LAST_QUARTER
NEXT_QUARTERStarts 00:00:00 of the next quarter and continues to the end of that quarter.SELECT Id FROM Account WHERE CreatedDate < NEXT_QUARTER
NEXT_N_QUARTERS:nStarts 00:00:00 of the next quarter and continues to the end of the nth quarter.SELECT Id FROM Account WHERE CreatedDate < NEXT_N_QUARTERS:2
LAST_N_QUARTERS:nStarts 00:00:00 of the previous quarter and continues to the end of the previous nth quarter.SELECT Id FROM Account WHERE CreatedDate > LAST_N_QUARTERS:2
THIS_YEARStarts 00:00:00 on January 1 of the current year and continues through the end of December 31 of the current year.SELECT Id FROM Opportunity WHERE CloseDate = THIS_YEAR
LAST_YEARStarts 00:00:00 on January 1 of the previous year and continues through the end of December 31 of that year.SELECT Id FROM Opportunity WHERE CloseDate > LAST_YEAR
NEXT_YEARStarts 00:00:00 on January 1 of the following year and continues through the end of December 31 of that year.SELECT Id FROM Opportunity WHERE CloseDate < NEXT_YEAR
NEXT_N_YEARS:nStarts 00:00:00 on January 1 of the following year and continues through the end of December 31 of the nth year.SELECT Id FROM Opportunity WHERE CloseDate < NEXT_N_YEARS:5
LAST_N_YEARS:nStarts 00:00:00 on January 1 of the previous year and continues through the end of December 31 of the previous nth year.SELECT Id FROM Opportunity WHERE CloseDate > LAST_N_YEARS:5
THIS_FISCAL_QUARTERStarts 00:00:00 on the first day of the current fiscal quarter and continues through the end of the last day of the fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Account WHERE CreatedDate = THIS_FISCAL_QUARTER
LAST_FISCAL_QUARTERStarts 00:00:00 on the first day of the last fiscal quarter and continues through the end of the last day of that fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Account WHERE CreatedDate > LAST_FISCAL_QUARTER
NEXT_FISCAL_QUARTERStarts 00:00:00 on the first day of the next fiscal quarter and continues through the end of the last day of that fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Account WHERE CreatedDate < NEXT_FISCAL_QUARTER
NEXT_N_FISCAL_​QUARTERS:nStarts 00:00:00 on the first day of the next fiscal quarter and continues through the end of the last day of the nth fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Account WHERE CreatedDate < NEXT_N_FISCAL_QUARTERS:6
LAST_N_FISCAL_​QUARTERS:nStarts 00:00:00 on the first day of the last fiscal quarter and continues through the end of the last day of the previous nth fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Account WHERE CreatedDate > LAST_N_FISCAL_QUARTERS:6
THIS_FISCAL_YEARStarts 00:00:00 on the first day of the current fiscal year and continues through the end of the last day of the fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Opportunity WHERE CloseDate = THIS_FISCAL_YEAR
LAST_FISCAL_YEARStarts 00:00:00 on the first day of the last fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Opportunity WHERE CloseDate > LAST_FISCAL_YEAR
NEXT_FISCAL_YEARStarts 00:00:00 on the first day of the next fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Opportunity WHERE CloseDate < NEXT_FISCAL_YEAR
NEXT_N_FISCAL_​YEARS:nStarts 00:00:00 on the first day of the next fiscal year and continues through the end of the last day of the nth fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Opportunity WHERE CloseDate < NEXT_N_FISCAL_YEARS:3
LAST_N_FISCAL_​YEARS:nStarts 00:00:00 on the first day of the last fiscal year and continues through the end of the last day of the previous nth fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Opportunity WHERE CloseDate > LAST_N_FISCAL_YEARS:3

Salesforce 获取Salesforce Token
wangyi9896的博客
03-18 2777
//获取Salesforce Token public String getSalesforceToken() { String getTokenUrl = 'https://login.salesforce.com/services/oauth2/token'; String grant_type = 'password'; Str...
用于kettle连接sap
04-08
用于kettle连接sap 包括sapjco3.dll，sapjco3.jar，sapjco3.pdb
Salesforce项目中使用ETL工具做数据迁移
sf_wilson
02-14 1040
【前言】：前4年中，参与过的数据迁移工作并不频繁，而且使用的方法也很传统，大多数重复性的工作都是手动对csv源文件在Excel上做导入前的处理，大体包括： #1. 数据mapping（有时需要将原对象拆表满足CRM数据结构） #2. 统一数据类型 #3. 数据清洗，如去重 / 调整数据格式（譬如，将phone中的area code与phone分离） #4. 数据默认值填充 #5. 数据ownership的对应 这类工作看起来很简单（就是把旧系统数据转存在新系统中），因此在实施中很容易被客户和领导低估。
使用Pentaho Data Integration 5.0.3 (Kettle)整合数据库，Salesforce数据Demo
03-28
使用Pentaho Data Integration 5.0.3整合SQL Server数据库和Salesforce数据的一个Demo，包括ppt，kettle文件及所有数据库backup文件及参考文档及代码。
使用kettle同步数据库表至其他库
11-16
使用kettle同步数据库表。网上找教程找了一天。终于弄好了。将两个数据库连接改成你自己的。sql也要改，然后把后面步骤的字段重新获取一下。然后就可以同步数据了。数据来源表增删改都可以同步。参考一下吧
kettle学习日记(Pentaho Data Integration)
BHSZZY的博客
04-07 1971
一、什么是kettle ●一个用java开发的软件，可以对数据进行一系列操作，例如数据整合、数据转换、数据迁移、数据导出报表等。 ●Kettle其实叫Pentaho Data Integration，Pentaho是公司名，Data Integration是数据整合。 ●Pentaho Data Integration分为商业版与开源版，在中国，一般人仍习惯把Pentaho Data Integration的开源版称为Kettle。 二、常用功能 本人所在的公司常用kettle把某个数据库中.
ETL之Kettle工具十大功能特性详解
xsfqh的专栏
09-10 5789
概述 Kettle是一款国外开源的ETL工具，纯java编写，可以在Window、Linux、Unix上运行。 说白了就是，很有必要去理解一般ETL工具必备的特性和功能，这样才更好的掌握Kettle的使用。今天主要先描述ETL工具的通用功能。 ETL工具的功能之一：连接 任何ETL工具都应该有能力连接到类型广泛的数据源和数据格式。对于最常用的关系型数据库系统，还要提供本地的连接方式（如对于Oracle的OCI），ETL应该能提供下面最基本的功能： （1）连接到普通关系型数据库并..
kettle连接salesforce
10-19
首先，我们需要在Kettle中配置Salesforce数据源，以便能够与Salesforce平台进行通信。我们需要提供Salesforce账户的凭据，例如用户名和密码。 接下来，我们可以使用Kettle的连接器（Connectors）功能来建立与...
使用Pentaho Data Integration 5.0.3 (Kettle)整合数据库（Salesforce数据Demo）
04-25
使用Pentaho Data Integration 5.0.3整合SQL Server数据库和Salesforce数据的一个Demo，包括ppt，kettle文件及所有数据库backup文件及参考文档及代码。
kettle中salesforce怎么用
最新发布
03-21
3. 读取Salesforce数据：在Kettle中创建一个新的转换，添加一个"Salesforce Input"步骤。在该步骤中，选择要读取的Salesforce对象和字段，并配置查询条件。运行转换时，该步骤将从Salesforce中读取数据。 4. 写入...
Kettle社区版，商业版，傲飞数据整合平台比较.pdf
08-27
此外，Kettle还支持Access、Excel、XML、CSV、TXT等文件数据源，甚至是Salesforce、RSS、Web服务、HTTP客户端等HTTP或REST协议的数据源。 | 数据源类型 | Kettle社区版 | Kettle商业版 | 傲飞数据整合平台 | |-----...
Salesforce系列（0）：Salesforce导入数据！
Mr_chen的博客
09-29 1715
Salesforce系列（0）：Salesforce导入数据！ 前言 今天博主将为大家分享Salesforce系列（0）：Salesforce导入数据！不喜勿喷，如有异议欢迎讨论！ 再看这篇文章前博主郑重推荐大家前往阅读我的VsCode安装汉化等系列文章，点这里跳转：VsCode系列（一）：下载安装及汉化 以及 Salesforce系列（一）：使用Visual Studio Code进行开发！ ...
Salesforce常用数据导入工具比较（Data Import Wizard与Data Loader）
sf_wilson
11-14 2620
【前言】： 我们在做数据迁移和日常数据导入时，需要考虑不同的导入工具是否满足当下的导入需求，本篇将以图文并茂的方式着重梳理下你所含糊不清的那些问题。 【工具特性】： 1. 数据去重，目前 【核心结论验证】： 1. 去重：Data Import Wizard能去重，但Data Loader不行。 在验证DIW的去重性，我们准备了如下CSV文件： Name Company Email...
Kettle构建Hadoop ETL实践（一）：ETL与Kettle
wzy0623的专栏
08-13 8836
目录 一、ETL基础 1. 数据仓库架构中的ETL 2. 数据抽取 （1）逻辑抽取 （2）物理抽取 （3）变化数据捕获 3. 数据转换 4. 数据装载 5. 开发ETL系统的方法 二、ETL工具 1. ETL工具的产生 2. ETL工具的功能 （1）连接 （2）平台独立 （3）数据规模 （4）设计灵活性 （5）复用性 （6）扩展性 （7）数据转换 （8）测试和调试 （9）血统和影响分析 （10）日志和审计 三、Kettle简介 1. Kettle设计原则 2
使用kettle进行不同数据库的数据导入
虾哔哔的博客
11-20 9520
kettle下载地址：community.pentaho.com/projects/data-integration/一、准备工作1.解压下载下来的压缩包如:pdi-ce-6.1.0.1-196.zip 然后打开Spoon.bat 2.建立转换。 （在连接数据库的时候，可能会遇到数据库驱动包不存在的问题，下载好对应的驱动包放到kettle的lib文件夹重启一下软件就可以了。） 在文件->
使用kettle进行增量抽取数据
热门推荐
cbphan的博客
11-15 1万+
需求背景： 当下行业，做程序不在是一味的写增删改查了。数据汇聚、数据清洗、数据转换已成为普遍现象，本人所在的项目就是这样的一个情况。由于客户建设的系统数量多，业务分支复杂。虽同属一个主业务，但每次统筹决策查询数据时就犯难了。由于数据孤岛，关联查询成为最难解决的问题。为了解决这个问题，我需要做的就是将几十个系统数据全部汇聚在一起，方便后续的数据分析使用。 说到数据汇聚，我们通常采用的办法就是通过ETL工具进行抽取，也有通过Oracle触发器利用dblink方...
Salesforce Dataloader 命令行连接数据库以及导入导出数据常见注意事项
萧十一郎2022
10-25 6493
Salesforce Dataloader 导入数据几条注意事项 2011-06-24 23:44 一. 导入数据几条注意事项 1. Dataload的时候经常会遇到Trigger提示Too Many SOQL Error. 主要因为Trigger将SOQL放到了循环里了。要想改正这个错误需要修改Trigger，但是在不能修改Trigger的情况下可以将Datal
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

爱知菜

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值