親知らずの日記: 8月 2015

2015/08/30

[作業記録]curl を使って Web API の挙動を確かめる

Lingr の Web API の挙動を調べるために curl を使用したのでメモ。

$ curl http://lingr.com/api/session/create -d user=mikoto2000@gmail.com -d password=MY_PASSWORD
{"status":"ok","session":"sVe3d6","public_id":"9a5w2p","nickname":"mikoto2000","user":{"username":"mikoto2000","name":"mikoto2000"}}

$ curl http://lingr.com/api/user/get_rooms -d session=5adYDm
{"status":"ok","rooms":["golang","vim"]}

$ curl http://lingr.com/api/room/show -d session=5adYDm -d rooms=vim
...(snip)

2015/08/29

[Git]fork したリポジトリに fork 元の変更を取り込む

この話題。リモートを追加して、 fetch, merge --ff でいけるようだ。

@mikoto2000 git fetchして、git merge upstream /master --ffとかしてますね。upstreamはremote addで登録しておきます。
— hiroyuki sato (@hiroysato) 2015, 8月 29

git remote add upstream [fork元リポジトリ]
git fetch upstream
git merge upstream/master --ff

2015/08/24

[Embulk]embulk-input-salesforce_bulk の next config を試す

embulk-input-salesforce_bulk の next config を試す

昨日の下記記事の続き。 next config がうまくいくかどうか確認していなかったのでそのあたりを確認。

親知らずの日記: [Embulk] Embulk で Salesforce から PostgreSQL にデータを転送してみた

設定変更

startRowMarkerName を追加。更新するたびに増えていく値であれば何でもよい。とはいえ一番簡単なのは最終更新日であろう。

in:
  type: salesforce_bulk
  userName: USERNAME
  password: PASSWORD+TOKEN
  authEndpointUrl: https://login.salesforce.com/services/Soap/u/34.0
  objectType: Account
  pollingIntervalMillisecond: 2500
  querySelectFrom: SELECT Id,Name,LastModifiedDate FROM Account
  columns:
  - {type: string, name: Id}
  - {type: string, name: Name}
  - {type: timestamp, name: LastModifiedDate, format: '%FT%T.%L%Z'}
  startRowMarkerName: LastModifiedDate
out: {type: postgresql, host: 192.168.11.207, user: from_sfdc, password: testing,
  database: from_sfdc, table: account_t, mode: merge}

初回実行

$ ./bin/embulk run -L ../embulk-input-salesforce_bulk/ ./sfdc_postgresql.yml -o ./sfdc_postgresql.yml
2015-08-24 22:39:52.733 +0900: Embulk v0.7.3
2015-08-24 22:39:54.269 +0900 [INFO] (transaction): Loaded plugin embulk-input-salesforce_bulk (0.1.0)
2015-08-24 22:39:54.309 +0900 [INFO] (transaction): Loaded plugin embulk-output-postgresql (0.4.1)
2015-08-24 22:39:54.372 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-24 22:39:54.425 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-24 22:39:54.427 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:39:54.427 +0900 [INFO] (transaction): Using merge mode
2015-08-24 22:39:54.446 +0900 [INFO] (transaction): SQL: DROP TABLE IF EXISTS "account_t_aa0b9f76c0_bl_tmp000"
2015-08-24 22:39:54.447 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:39:54.455 +0900 [INFO] (transaction): SQL: CREATE TABLE IF NOT EXISTS "account_t_aa0b9f76c0_bl_tmp000" ("Id" VARCHAR(20), "Name" VARCHAR(100), "LastModifiedDate" TIMESTAMP)
2015-08-24 22:39:54.457 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:39:54.468 +0900 [INFO] (transaction): Using merge keys: [Id]
2015-08-24 22:39:54.488 +0900 [INFO] (transaction): {done:  0 / 1, running: 0}
2015-08-24 22:39:54.511 +0900 [INFO] (task-0000): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-24 22:39:54.521 +0900 [INFO] (task-0000): SQL: SET search_path TO "public"
2015-08-24 22:39:54.522 +0900 [INFO] (task-0000): > 0.00 seconds
2015-08-24 22:39:54.523 +0900 [INFO] (task-0000): Copy SQL: COPY "account_t_aa0b9f76c0_bl_tmp000" ("Id", "Name", "LastModifiedDate") FROM STDIN
2015-08-24 22:39:54.525 +0900 [WARN] (task-0000): An output plugin is compiled with old Embulk plugin API. Please update the plugin version using "embulk gem install" command, or contact a developer of the plugin to upgrade the plugin code using "embulk migrate" command: class org.embulk.output.jdbc.AbstractJdbcOutputPlugin$PluginPageOutput
2015-08-24 22:39:54.548 +0900 [INFO] (task-0000): Try login to 'https://login.salesforce.com/services/Soap/u/34.0'.
2015-08-24 22:39:55.016 +0900 [INFO] (task-0000): Login success.
2015-08-24 22:39:55.017 +0900 [INFO] (task-0000): Send request : 'SELECT Id,Name,LastModifiedDate FROM Account'
2015-08-24 22:39:58.396 +0900 [INFO] (task-0000): Loading 30 rows (1,903 bytes)
2015-08-24 22:39:58.399 +0900 [INFO] (task-0000): > 0.00 seconds (loaded 30 rows in total)
2015-08-24 22:39:58.524 +0900 [INFO] (transaction): {done:  1 / 1, running: 0}
2015-08-24 22:39:58.525 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=28800}
2015-08-24 22:39:58.719 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-24 22:39:58.720 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:39:58.720 +0900 [INFO] (transaction): SQL: WITH updated AS (UPDATE "account_t" SET "Id" = S."Id", "Name" = S."Name", "LastModifiedDate" = S."LastModifiedDate" FROM (SELECT "Id", "Name", "LastModifiedDate" FROM "account_t_aa0b9f76c0_bl_tmp000") S WHERE "account_t"."Id" = S."Id" RETURNING S."Id") INSERT INTO "account_t" ("Id", "Name", "LastModifiedDate") SELECT DISTINCT ON ("Id") * FROM (SELECT "Id", "Name", "LastModifiedDate" FROM "account_t_aa0b9f76c0_bl_tmp000") S WHERE NOT EXISTS (SELECT 1 FROM updated WHERE S."Id" = updated."Id")
2015-08-24 22:39:58.723 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:39:58.729 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-24 22:39:58.735 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-24 22:39:58.736 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:39:58.736 +0900 [INFO] (transaction): SQL: DROP TABLE IF EXISTS "account_t_aa0b9f76c0_bl_tmp000"
2015-08-24 22:39:58.738 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:39:58.740 +0900 [INFO] (main): Committed.
2015-08-24 22:39:58.741 +0900 [INFO] (main): Next config diff: {"in":{"start_row_marker":"2015-08-24T13:31:58.000Z"},"out":{}}

ちゃんと 30 行ロードされたようだ。

2015-08-24 22:39:58.396 +0900 [INFO] (task-0000): Loading 30 rows (1,903 bytes)
2015-08-24 22:39:58.399 +0900 [INFO] (task-0000): > 0.00 seconds (loaded 30 rows in total)

そして更新された設定ファイルがこちら。

in:
  type: salesforce_bulk
  userName: USERNAME
  password: PASSWORD+TOKEN
  authEndpointUrl: https://login.salesforce.com/services/Soap/u/34.0
  objectType: Account
  pollingIntervalMillisecond: 2500
  querySelectFrom: SELECT Id,Name,LastModifiedDate FROM Account
  columns:
  - {type: string, name: Id}
  - {type: string, name: Name}
  - {type: timestamp, name: LastModifiedDate, format: '%FT%T.%L%Z'}
  startRowMarkerName: LastModifiedDate
  start_row_marker: '2015-08-24T13:31:58.000Z'
out: {type: postgresql, host: 192.168.11.207, user: from_sfdc, password: testing,
  database: from_sfdc, table: account_t, mode: merge}

2 回目実行

Salesforce 側を何も更新せずに実行。

$ ./bin/embulk run -L ../embulk-input-salesforce_bulk/ ./sfdc_postgresql.yml -o ./sfdc_postgresql.yml
2015-08-24 22:42:51.365 +0900: Embulk v0.7.3
2015-08-24 22:42:52.903 +0900 [INFO] (transaction): Loaded plugin embulk-input-salesforce_bulk (0.1.0)
2015-08-24 22:42:52.930 +0900 [INFO] (transaction): Loaded plugin embulk-output-postgresql (0.4.1)
2015-08-24 22:42:52.991 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-24 22:42:53.040 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-24 22:42:53.043 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:42:53.043 +0900 [INFO] (transaction): Using merge mode
2015-08-24 22:42:53.072 +0900 [INFO] (transaction): SQL: DROP TABLE IF EXISTS "account_t_5c31b5d480_bl_tmp000"
2015-08-24 22:42:53.074 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:42:53.081 +0900 [INFO] (transaction): SQL: CREATE TABLE IF NOT EXISTS "account_t_5c31b5d480_bl_tmp000" ("Id" VARCHAR(20), "Name" VARCHAR(100), "LastModifiedDate" TIMESTAMP)
2015-08-24 22:42:53.104 +0900 [INFO] (transaction): > 0.02 seconds
2015-08-24 22:42:53.121 +0900 [INFO] (transaction): Using merge keys: [Id]
2015-08-24 22:42:53.145 +0900 [INFO] (transaction): {done:  0 / 1, running: 0}
2015-08-24 22:42:53.170 +0900 [INFO] (task-0000): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-24 22:42:53.179 +0900 [INFO] (task-0000): SQL: SET search_path TO "public"
2015-08-24 22:42:53.180 +0900 [INFO] (task-0000): > 0.00 seconds
2015-08-24 22:42:53.181 +0900 [INFO] (task-0000): Copy SQL: COPY "account_t_5c31b5d480_bl_tmp000" ("Id", "Name", "LastModifiedDate") FROM STDIN
2015-08-24 22:42:53.183 +0900 [WARN] (task-0000): An output plugin is compiled with old Embulk plugin API. Please update the plugin version using "embulk gem install" command, or contact a developer of the plugin to upgrade the plugin code using "embulk migrate" command: class org.embulk.output.jdbc.AbstractJdbcOutputPlugin$PluginPageOutput
2015-08-24 22:42:53.203 +0900 [INFO] (task-0000): Try login to 'https://login.salesforce.com/services/Soap/u/34.0'.
2015-08-24 22:42:53.653 +0900 [INFO] (task-0000): Login success.
2015-08-24 22:42:53.654 +0900 [INFO] (task-0000): Send request : 'SELECT Id,Name,LastModifiedDate FROM Account WHERE LastModifiedDate > 2015-08-24T13:31:58.000Z'
2015-08-24 22:42:57.647 +0900 [INFO] (transaction): {done:  1 / 1, running: 0}
2015-08-24 22:42:57.648 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=28800}
2015-08-24 22:42:57.651 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-24 22:42:57.652 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:42:57.652 +0900 [INFO] (transaction): SQL: WITH updated AS (UPDATE "account_t" SET "Id" = S."Id", "Name" = S."Name", "LastModifiedDate" = S."LastModifiedDate" FROM (SELECT "Id", "Name", "LastModifiedDate" FROM "account_t_5c31b5d480_bl_tmp000") S WHERE "account_t"."Id" = S."Id" RETURNING S."Id") INSERT INTO "account_t" ("Id", "Name", "LastModifiedDate") SELECT DISTINCT ON ("Id") * FROM (SELECT "Id", "Name", "LastModifiedDate" FROM "account_t_5c31b5d480_bl_tmp000") S WHERE NOT EXISTS (SELECT 1 FROM updated WHERE S."Id" = updated."Id")
2015-08-24 22:42:57.654 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:42:57.660 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-24 22:42:57.668 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-24 22:42:57.669 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:42:57.669 +0900 [INFO] (transaction): SQL: DROP TABLE IF EXISTS "account_t_5c31b5d480_bl_tmp000"
2015-08-24 22:42:57.672 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:42:57.675 +0900 [INFO] (main): Committed.
2015-08-24 22:42:57.676 +0900 [INFO] (main): Next config diff: {"in":{"start_row_marker":"2015-08-24T13:31:58.000Z"},"out":{}}

「Loading XXX rows」がない。何も読み込まれなかったようだ。

3 回目実行

取引先を一つ修正してから実行。

$ ./bin/embulk run -L ../embulk-input-salesforce_bulk/ ./sfdc_postgresql.yml -o ./sfdc_postgresql.yml
2015-08-24 22:45:26.724 +0900: Embulk v0.7.3
2015-08-24 22:45:28.289 +0900 [INFO] (transaction): Loaded plugin embulk-input-salesforce_bulk (0.1.0)
2015-08-24 22:45:28.321 +0900 [INFO] (transaction): Loaded plugin embulk-output-postgresql (0.4.1)
2015-08-24 22:45:28.392 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-24 22:45:28.448 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-24 22:45:28.450 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:45:28.450 +0900 [INFO] (transaction): Using merge mode
2015-08-24 22:45:28.468 +0900 [INFO] (transaction): SQL: DROP TABLE IF EXISTS "account_t_f80c474f80_bl_tmp000"
2015-08-24 22:45:28.470 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:45:28.476 +0900 [INFO] (transaction): SQL: CREATE TABLE IF NOT EXISTS "account_t_f80c474f80_bl_tmp000" ("Id" VARCHAR(20), "Name" VARCHAR(100), "LastModifiedDate" TIMESTAMP)
2015-08-24 22:45:28.477 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:45:28.523 +0900 [INFO] (transaction): Using merge keys: [Id]
2015-08-24 22:45:28.542 +0900 [INFO] (transaction): {done:  0 / 1, running: 0}
2015-08-24 22:45:28.561 +0900 [INFO] (task-0000): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-24 22:45:28.569 +0900 [INFO] (task-0000): SQL: SET search_path TO "public"
2015-08-24 22:45:28.570 +0900 [INFO] (task-0000): > 0.00 seconds
2015-08-24 22:45:28.570 +0900 [INFO] (task-0000): Copy SQL: COPY "account_t_f80c474f80_bl_tmp000" ("Id", "Name", "LastModifiedDate") FROM STDIN
2015-08-24 22:45:28.572 +0900 [WARN] (task-0000): An output plugin is compiled with old Embulk plugin API. Please update the plugin version using "embulk gem install" command, or contact a developer of the plugin to upgrade the plugin code using "embulk migrate" command: class org.embulk.output.jdbc.AbstractJdbcOutputPlugin$PluginPageOutput
2015-08-24 22:45:28.587 +0900 [INFO] (task-0000): Try login to 'https://login.salesforce.com/services/Soap/u/34.0'.
2015-08-24 22:45:29.355 +0900 [INFO] (task-0000): Login success.
2015-08-24 22:45:29.355 +0900 [INFO] (task-0000): Send request : 'SELECT Id,Name,LastModifiedDate FROM Account WHERE LastModifiedDate > 2015-08-24T13:31:58.000Z'
2015-08-24 22:45:32.773 +0900 [INFO] (task-0000): Loading 1 rows (57 bytes)
2015-08-24 22:45:32.777 +0900 [INFO] (task-0000): > 0.00 seconds (loaded 1 rows in total)
2015-08-24 22:45:32.958 +0900 [INFO] (transaction): {done:  1 / 1, running: 0}
2015-08-24 22:45:32.959 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=28800}
2015-08-24 22:45:32.964 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-24 22:45:32.965 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:45:32.965 +0900 [INFO] (transaction): SQL: WITH updated AS (UPDATE "account_t" SET "Id" = S."Id", "Name" = S."Name", "LastModifiedDate" = S."LastModifiedDate" FROM (SELECT "Id", "Name", "LastModifiedDate" FROM "account_t_f80c474f80_bl_tmp000") S WHERE "account_t"."Id" = S."Id" RETURNING S."Id") INSERT INTO "account_t" ("Id", "Name", "LastModifiedDate") SELECT DISTINCT ON ("Id") * FROM (SELECT "Id", "Name", "LastModifiedDate" FROM "account_t_f80c474f80_bl_tmp000") S WHERE NOT EXISTS (SELECT 1 FROM updated WHERE S."Id" = updated."Id")
2015-08-24 22:45:32.967 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:45:32.973 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-24 22:45:32.978 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-24 22:45:32.979 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-24 22:45:32.980 +0900 [INFO] (transaction): SQL: DROP TABLE IF EXISTS "account_t_f80c474f80_bl_tmp000"
2015-08-24 22:45:32.993 +0900 [INFO] (transaction): > 0.01 seconds
2015-08-24 22:45:32.997 +0900 [INFO] (main): Committed.
2015-08-24 22:45:32.997 +0900 [INFO] (main): Next config diff: {"in":{"start_row_marker":"2015-08-24T13:45:28.000Z"},"out":{}}

うむ、良いのではないだろうか。

PostgreSQL のカラムが更新されていることも確認。embulk-input-postgresql の mode も merge で良いようだ。

2015/08/23

[Embulk] Embulk で Salesforce から PostgreSQL にデータを転送してみた

使用するプラグイン
Embulk に embulk-output-postgresql を入れる
PostgreSQL を準備する
- VirtualBox に Debian minimal をインストール
- Debian に PostgreSQL 環境を構築
Embulk 設定ファイルを作成
実行

使用するプラグイン

salesforce_bulk は、自作プラグイン。設定方法などまだまだいろいろ変わりそうなので gem 化は考えていない。

Embulk に embulk-output-postgresql を入れる

$ ./bin/embulk gem install embulk-output-postgresql
2015-08-22 22:24:01.986 +0900: Embulk v0.7.2
Successfully installed embulk-output-postgresql-0.4.1
1 gem installed

PostgreSQL を準備する

VirtualBox に Debian minimal をインストール

...(略)

Debian に PostgreSQL 環境を構築

aptitude update
aptitude full-upgrade
aptitude install postgresql

su - postgres
psql

create database from_sfdc;
\c from_sfdc
create role from_sfdc with login;
alter user from_sfdc with password 'testing';

create table account_t("Id" varchar(20) primary key, "Name" varchar(100), "LastModifiedDate" timestamp);

exit
echo "listen_addresses = '*'" >> /etc/postgresql/9.4/main/postgres.conf
echo "host from_sfdc from_sfdc xxx.xxx.xxx.xxx/xx md5" >> /etc/postgresql/9.4/main/pg_hba.conf
service postgresql restart

Embulk 設定ファイルを作成

in:
  type: salesforce_bulk
  userName: USERNAME
  password: PASSWORD+TOKEN
  authEndpointUrl: https://login.salesforce.com/services/Soap/u/34.0
  objectType: Account
  pollingIntervalMillisecond: 2500
  querySelectFrom: SELECT Id,Name,LastModifiedDate FROM Account
  columns:
  - {type: string, name: Id}
  - {type: string, name: Name}
  - {type: timestamp, name: LastModifiedDate, format: '%FT%T.%L%Z'}
out:
  type: postgresql
  host: 192.168.11.207
  user: from_sfdc
  password: "testing"
  database: from_sfdc
  table: account_t
  mode: merge

実行

$ ./bin/embulk run -L ../embulk-input-salesforce_bulk/ ./sfdc_postgresql.yml
2015-08-23 03:08:56.728 +0900: Embulk v0.7.2
2015-08-23 03:08:58.254 +0900 [INFO] (transaction): Loaded plugin embulk-input-salesforce_bulk (0.1.0)
2015-08-23 03:08:58.289 +0900 [INFO] (transaction): Loaded plugin embulk-output-postgresql (0.4.1)
2015-08-23 03:08:58.356 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-23 03:08:58.406 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-23 03:08:58.408 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-23 03:08:58.408 +0900 [INFO] (transaction): Using merge mode
2015-08-23 03:08:58.427 +0900 [INFO] (transaction): SQL: DROP TABLE IF EXISTS "account_t_ba0b162280_bl_tmp000"
2015-08-23 03:08:58.429 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-23 03:08:58.435 +0900 [INFO] (transaction): SQL: CREATE TABLE IF NOT EXISTS "account_t_ba0b162280_bl_tmp000" ("Id" VARCHAR(20), "Name" VARCHAR(100), "LastModifiedDate" TIMESTAMP)
2015-08-23 03:08:58.436 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-23 03:08:58.466 +0900 [INFO] (transaction): Using merge keys: [Id]
2015-08-23 03:08:58.482 +0900 [INFO] (transaction): {done:  0 / 1, running: 0}
2015-08-23 03:08:58.500 +0900 [INFO] (task-0000): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-23 03:08:58.506 +0900 [INFO] (task-0000): SQL: SET search_path TO "public"
2015-08-23 03:08:58.507 +0900 [INFO] (task-0000): > 0.00 seconds
2015-08-23 03:08:58.508 +0900 [INFO] (task-0000): Copy SQL: COPY "account_t_ba0b162280_bl_tmp000" ("Id", "Name", "LastModifiedDate") FROM STDIN
2015-08-23 03:08:58.509 +0900 [WARN] (task-0000): An output plugin is compiled with old Embulk plugin API. Please update the plugin version using "embulk gem install" command, or contact a developer of the plugin to upgrade the plugin code using "embulk migrate" command: class org.embulk.output.jdbc.AbstractJdbcOutputPlugin$PluginPageOutput
2015-08-23 03:08:58.524 +0900 [INFO] (task-0000): Try login to 'https://login.salesforce.com/services/Soap/u/34.0'.
2015-08-23 03:08:58.960 +0900 [INFO] (task-0000): Login success.
2015-08-23 03:08:58.960 +0900 [INFO] (task-0000): Send request : 'SELECT Id,Name,LastModifiedDate FROM Account'
2015-08-23 03:09:02.660 +0900 [INFO] (task-0000): Loading 30 rows (1,903 bytes)
2015-08-23 03:09:02.663 +0900 [INFO] (task-0000): > 0.00 seconds (loaded 30 rows in total)
2015-08-23 03:09:02.730 +0900 [INFO] (transaction): {done:  1 / 1, running: 0}
2015-08-23 03:09:02.731 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=28800}
2015-08-23 03:09:02.735 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-23 03:09:02.736 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-23 03:09:02.736 +0900 [INFO] (transaction): SQL: WITH updated AS (UPDATE "account_t" SET "Id" = S."Id", "Name" = S."Name", "LastModifiedDate" = S."LastModifiedDate" FROM (SELECT "Id", "Name", "LastModifiedDate" FROM "account_t_ba0b162280_bl_tmp000") S WHERE "account_t"."Id" = S."Id" RETURNING S."Id") INSERT INTO "account_t" ("Id", "Name", "LastModifiedDate") SELECT DISTINCT ON ("Id") * FROM (SELECT "Id", "Name", "LastModifiedDate" FROM "account_t_ba0b162280_bl_tmp000") S WHERE NOT EXISTS (SELECT 1 FROM updated WHERE S."Id" = updated."Id")
2015-08-23 03:09:02.739 +0900 [INFO] (transaction): > 0.00 seconds (30 rows)
2015-08-23 03:09:02.746 +0900 [INFO] (transaction): Connecting to jdbc:postgresql://192.168.11.207:5432/from_sfdc options {user=from_sfdc, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2015-08-23 03:09:02.751 +0900 [INFO] (transaction): SQL: SET search_path TO "public"
2015-08-23 03:09:02.752 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-23 03:09:02.752 +0900 [INFO] (transaction): SQL: DROP TABLE IF EXISTS "account_t_ba0b162280_bl_tmp000"
2015-08-23 03:09:02.755 +0900 [INFO] (transaction): > 0.00 seconds
2015-08-23 03:09:02.759 +0900 [INFO] (main): Committed.
2015-08-23 03:09:02.759 +0900 [INFO] (main): Next config diff: {"in":{},"out":{}}

無事、PostgreSQL に格納された。よかった。

こんな感じのこともありました。『中の処理がわからんとシンタックスエラーっていわれても...』ってなって割と苦戦してました。

embulk-output-postgresql, 「Error: java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"」って怒られるな。
— 大雪命 (@mikoto2000) 2015, 8月 22

@mikoto2000 カラムをダブルクォーテーションで囲み忘れてて全部小文字になってた -> Schema のカラム名と食い違って無視される -> SQL 文のカラム定義が空になる -> Syntax error。のようだ。
— 大雪命 (@mikoto2000) 2015, 8月 22

登録: 投稿 (Atom)