embulkバルクインサート時に、google apis client error

Question

環境：oracle 12c、embulk 0.9.15、において、bigqueryへバルクインサート中に下記に示すエラーが発生致しました。

bigquery検証版（US環境）では出力されなかったエラーです。
bigquery本番（日本：豊洲）に切り替えて実装してからエラーが出力されました。

※Oracle定義

SQL> desc T_RS_TRAN
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 ORDER_NO                                  NOT NULL NUMBER(15)
 RS_TYPE                                   NOT NULL VARCHAR2(1)
 FACTORY_CODE                              NOT NULL VARCHAR2(4)
 ORG_CODE                                  NOT NULL VARCHAR2(3)
 TRAN_DATE                                 NOT NULL DATE
 HINBAN                                    NOT NULL VARCHAR2(40)
 QTY                                       NOT NULL NUMBER(11,2)
 SLIP_NO                                   NOT NULL VARCHAR2(10)
 ITEM_NO                                   NOT NULL VARCHAR2(2)
 DIVISION_NO                                        NUMBER(1)
 ●●
 ●●
 ●●
 INV_INFO_UPDATE_FLAG                               VARCHAR2(1)


※GCPの認証ファイル

srdcs@SSVR0224:/home1/bigquery/conf$ cat '/home1/bigquery/conf/YZK GCP Project-8bc479ac2a95.json'
{
  "type": "service_account",
  "project_id": "yzk-gcp-project",
  "private_key_id": "●●●●●●●",
  "private_key": "-----BEGIN PRIVATE KEY-----●●●●●●●-----END PRIVATE KEY-----\n",
  "client_email": "●●●●●●●",
  "client_id": "●●●●●●●",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/yzk-srdcs-gmail-com%40yzk-gcp-project.iam.gserviceaccount.com"
}


※embulk定義
srdcs@SSVR0224:/home1/bigquery/conf$ cat config_T_RS_TRAN_split_update.yml
in:
  type: oracle
  path_prefix: null
  driver_path: /opt/oracle/product/12.1.0/db/jdbc/lib/ojdbc7.jar
  host: SSVR0224
  user: srdcs
  password: srdcs
  database: srdcsc
  port: 1533
  table: T_RS_TRAN
out: {type: bigquery, auth_method: json_key, json_keyfile: '/home1/bigquery/conf/YZK GCP Project-8bc479ac2a95.json', project: yzk-gcp-project, dataset: srdcs_data, auto_create_table: true, table: T_RS_TRAN_SSVR0224, open_read_timeout_sec: 360000, send_timeout_sec: 360000, read_timeout_sec: 360000}


※embulk実行ログ

srdcs@SSVR0224:/home1/bigquery/conf$ $embulk run config_T_RS_TRAN_split_update.yml
2019-04-16 08:37:07.965 +0900: Embulk v0.9.15
2019-04-16 08:37:08.351 +0900 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
2019-04-16 08:37:10.283 +0900 [INFO] (main): Gem's home and path are set by default: "/export/home/srdcs/.embulk/lib/gems"
2019-04-16 08:37:10.918 +0900 [INFO] (main): Started Embulk v0.9.15
2019-04-16 08:37:10.989 +0900 [INFO] (0001:transaction): Loaded plugin embulk-input-oracle (0.9.3)
io/console on JRuby shells out to stty for most operations
2019-04-16 08:37:13.686 +0900 [INFO] (0001:transaction): Loaded plugin embulk-output-bigquery (0.4.9)
2019-04-16 08:37:13.723 +0900 [INFO] (0001:transaction): Connecting to jdbc:oracle:thin:@SSVR0224:1533:srdcsc options {oracle.jdbc.ReadTimeout=1800000, user=srdcs, password=***, oracle.net.CONNECT_TIMEOUT=300000}
2019-04-16 08:37:14.071 +0900 [INFO] (0001:transaction): Using JDBC Driver 12.1.0.2.0
2019-04-16 08:37:14.178 +0900 [INFO] (0001:transaction): Using local thread executor with max_threads=48 / output tasks 24 = input tasks 1 * 24
2019-04-16 08:37:14.283 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Get dataset... yzk-gcp-project:srdcs_data
2019-04-16 08:37:16.606 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Create table... yzk-gcp-project:srdcs_data.LOAD_TEMP_f2bbd947_72a0_47ed_8ac4_86f226fe464a_T_RS_TRAN_SSVR0224
2019-04-16 08:37:17.399 +0900 [INFO] (0001:transaction): {done:  0 / 1, running: 0}
2019-04-16 08:37:17.648 +0900 [INFO] (0049:task-0000): Connecting to jdbc:oracle:thin:@SSVR0224:1533:srdcsc options {oracle.jdbc.ReadTimeout=1800000, user=srdcs, password=***, oracle.net.CONNECT_TIMEOUT=300000}
2019-04-16 08:37:17.746 +0900 [INFO] (0049:task-0000): SQL: SELECT * FROM "T_RS_TRAN"
2019-04-16 08:37:18.019 +0900 [INFO] (0049:task-0000): > 0.27 seconds
2019-04-16 08:37:18.047 +0900 [INFO] (embulk-output-executor-0): embulk-output-bigquery: create /var/tmp/embulk_output_bigquery_20190415-22463-r8aq15.22463.2004.csv
2019-04-16 08:37:18.055 +0900 [INFO] (embulk-output-executor-1): embulk-output-bigquery: create /var/tmp/embulk_output_bigquery_20190415-22463-r8aq15.22463.2006.csv
2019-04-16 08:37:18.073 +0900 [INFO] (embulk-output-executor-2): embulk-output-bigquery: create /var/tmp/embulk_output_bigquery_20190415-22463-r8aq15.22463.2008.csv
2019-04-16 08:37:48.652 +0900 [INFO] (0001:transaction): embulk-output-bigquery: delete /var/tmp/embulk_output_bigquery_20190415-22463-r8aq15.22463.2048.csv
2019-04-16 08:37:48.653 +0900 [INFO] (0001:transaction): embulk-output-bigquery: delete /var/tmp/embulk_output_bigquery_20190415-22463-r8aq15.22463.2050.csv
org.embulk.exec.PartialExecutionException: org.jruby.exceptions.RaiseException: (Error) failed to load /var/tmp/embulk_output_bigquery_20190415-22463-r8aq15.22463.2044.csv to yzk-gcp-project:srdcs_data.LOAD_TEMP_f2bbd947_72a0_47ed_8ac4_86f226fe464a_T_RS_TRAN_SSVR0224 in us/eu, response:{:status_code=>404, :message=>"notFound: Not found: Job yzk-gcp-project:embulk_load_job_3f4d3780-9e00-473c-998c-1eeb62f67186", :error_class=>Google::Apis::ClientError}
        at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:340)
        at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:566)
        at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:35)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
        at org.embulk.spi.Exec.doWith(Exec.java:22)
        at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
        at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:178)
        at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:292)
        at org.embulk.EmbulkRunner.run(EmbulkRunner.java:156)
        at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:436)
        at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:91)
        at org.embulk.cli.Main.main(Main.java:64)
Caused by: org.jruby.exceptions.RaiseException: (Error) failed to load /var/tmp/embulk_output_bigquery_20190415-22463-r8aq15.22463.2044.csv to yzk-gcp-project:srdcs_data.LOAD_TEMP_f2bbd947_72a0_47ed_8ac4_86f226fe464a_T_RS_TRAN_SSVR0224 in us/eu, response:{:status_code=>404, :message=>"notFound: Not found: Job yzk-gcp-project:embulk_load_job_3f4d3780-9e00-473c-998c-1eeb62f67186", :error_class=>Google::Apis::ClientError}
        at RUBY.block in load(/export/home/srdcs/.embulk/lib/gems/gems/embulk-output-bigquery-0.4.9/lib/embulk/output/bigquery/bigquery_client.rb:243)
        at RUBY.with_job_retry(/export/home/srdcs/.embulk/lib/gems/gems/embulk-output-bigquery-0.4.9/lib/embulk/output/bigquery/bigquery_client.rb:58)
        at RUBY.load(/export/home/srdcs/.embulk/lib/gems/gems/embulk-output-bigquery-0.4.9/lib/embulk/output/bigquery/bigquery_client.rb:172)
        at RUBY.block in load_in_parallel(/export/home/srdcs/.embulk/lib/gems/gems/embulk-output-bigquery-0.4.9/lib/embulk/output/bigquery/bigquery_client.rb:160)

Error: org.jruby.exceptions.RaiseException: (Error) failed to load /var/tmp/embulk_output_bigquery_20190415-22463-r8aq15.22463.2044.csv to yzk-gcp-project:srdcs_data.LOAD_TEMP_f2bbd947_72a0_47ed_8ac4_86f226fe464a_T_RS_TRAN_SSVR0224 in us/eu, response:{:status_code=>404, :message=>"notFound: Not found: Job yzk-gcp-project:embulk_load_job_3f4d3780-9e00-473c-998c-1eeb62f67186", :error_class=>Google::Apis::ClientError}

Comments

[Orlofsky]

>bigquery本番（日本：豊洲）に切り替えて実装してからエラーが出力されました。
津波が来たら水没しませんか？電源を確保し続けられるかも心配だし。

[raccoondog]

そうですね。。。

Answer 1

本番とテストではどのように設定を変えられたのでしょうか？

Bigqueryの設定はよくわかりませんが、locationとかが必要なのではないでしょうか？
Bigqueryの設定もこういう風に書いていただけると読みやすいです。

out:
  type: bigquery
  auth_method: json_key
  json_keyfile: '/home1/bigquery/conf/YZK GCP Project-8bc479ac2a95.json'
  project: yzk-gcp-project
  dataset: srdcs_data
  auto_create_table: true
  table: T_RS_TRAN_SSVR0224
  open_read_timeout_sec: 360000
  send_timeout_sec: 360000
  read_timeout_sec: 360000

Comments

[raccoondog]

有難うございます。

embulkのout句で、location: asia-northeast1を指定して、成功しました。