teratail
回答率
85.37%
teratail 10th-anniversary
teratail 10th-anniversary
質問するログイン新規登録
トップに関する質問embulkのincremental_columns定義とOracleの定義不一致によるエラー

編集履歴

質問編集履歴

2

質問内容更新

2019/02/21 06:51

投稿

raccoondog
raccoondog

スコア79

test CHANGED
File without changes
test CHANGED
@@ -154,67 +154,59 @@
154
154
 
155
155
 
156
156
 
157
+
158
+
157
159
  ```ここに言語を入力
158
160
 
159
- ■Oracle 初回データ投入後
160
-
161
-
162
-
163
- SQL> select count(*) from T_RS_TRAN;
164
-
165
-
166
-
167
- COUNT(*)
168
-
169
- ----------
170
-
171
- 999974
172
-
173
-
174
-
175
-
176
-
177
- ■embulk増分バルクインサート後の最終レコード（hinbanカラム）（diff_OraToBq.yml）
178
-
179
-
180
-
181
- in:
182
-
183
- last_record: [ZZZZ2W98FZCM]
184
-
185
- out: {}
186
-
187
-
188
-
189
-
190
-
191
- ■Oracle 2回目データ投入後
192
-
193
- SQL> select count(*) from T_RS_TRAN;
194
-
195
-
196
-
197
- COUNT(*)
198
-
199
- ----------
200
-
201
- 1999955
202
-
203
-
204
-
205
- ■Oracle データ内のhinbanカラム値を検索し、[ZZZZ2W98FZCM]は１つだけ
206
-
207
-
208
-
209
- SQL> select hinban from T_RS_TRAN where hinban = 'ZZZZ2W98FZCM';
210
-
211
-
212
-
213
- HINBAN
214
-
215
- ------------------------------------------------------------------
216
-
217
- ZZZZ2W98FZCM
161
+ ■embulkによるseed.ymlファイルのpreview結果
162
+
163
+
164
+
165
+ 2019-02-21 15:33:49.668 +0900: Embulk v0.9.15
166
+
167
+ 2019-02-21 15:33:50.042 +0900 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
168
+
169
+ 2019-02-21 15:33:52.433 +0900 [INFO] (main): Gem's home and path are set by default: "C:\Users\yazaki.embulk\lib\gems"
170
+
171
+ 2019-02-21 15:33:53.222 +0900 [INFO] (main): Started Embulk v0.9.15
172
+
173
+ 2019-02-21 15:33:53.284 +0900 [INFO] (0001:preview): Loaded plugin embulk-input-oracle (0.9.3)
174
+
175
+ 2019-02-21 15:33:53.315 +0900 [INFO] (0001:preview): Connecting to jdbc:oracle:thin:@A0430-02396:1521:ORCL options {oracle.jdbc.ReadTimeout=1800000, user=system, password=***, oracle.net.CONNECT_TIMEOUT=300000}
176
+
177
+ 2019-02-21 15:33:53.690 +0900 [INFO] (0001:preview): Using JDBC Driver 12.1.0.2.0
178
+
179
+ 2019-02-21 15:33:53.846 +0900 [INFO] (0001:preview): Connecting to jdbc:oracle:thin:@A0430-02396:1521:ORCL options {oracle.jdbc.ReadTimeout=1800000, user=system, password=***, oracle.net.CONNECT_TIMEOUT=300000}
180
+
181
+ 2019-02-21 15:33:53.939 +0900 [INFO] (0001:preview): SQL: SELECT * FROM "T_RS_TRAN" ORDER BY "ORDER_NO"
182
+
183
+ 2019-02-21 15:33:53.955 +0900 [INFO] (0001:preview): > 0.02 seconds
184
+
185
+ +-----------------+----------------+---------------------+-----------------+
186
+
187
+ | ORDER_NO:double | RS_TYPE:string | FACTORY_CODE:string | ORG_CODE:string |
188
+
189
+ +-----------------+----------------+---------------------+-----------------+
190
+
191
+ | 2.1002419E7 | 1 | 8000 | MQ9 |
192
+
193
+ | 2.100242E7 | 1 | 7500 | TWP |
194
+
195
+ | 2.100243E7 | 1 | 7500 | 5BN |
196
+
197
+
198
+
199
+
200
+
201
+
202
+
203
+ ■Oracleに取り込んだデータ
204
+
205
+
206
+
207
+ "21023317","2","8100","XD9",2018/06/26,"VHP5FUH3F78C",1,"38A8U","06",0,"ZZZ"
208
+
209
+ "21023321","2","8000","7J7",2018/07/14,"YT98FCU5ZSG5",50,"I0ZSCQX0","30",0,"
218
210
 
219
211
 
220
212
 
@@ -226,87 +218,15 @@
226
218
 
227
219
  ----------------------------------------- -------- ----------------------------
228
220
 
229
- HINBAN VARCHAR2(40)
230
-
231
-
232
-
233
-
234
-
235
-
236
-
237
- ■embulkで最終レコードを参照し、増分バルクインサート
238
-
239
-
240
-
241
- C:\Users\yazaki\Desktop\embulk>embulk-0.9.15.bat run config_OraToBq_incremental.yml.liquid -c diff_OraToBq.yml
242
-
243
- 2019-02-20 18:22:51.265 +0900: Embulk v0.9.15
244
-
245
- 2019-02-20 18:22:51.948 +0900 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
246
-
247
- 2019-02-20 18:22:55.562 +0900 [INFO] (main): Gem's home and path are set by default: "C:\Users\yazaki.embulk\lib\gems"
248
-
249
- 2019-02-20 18:22:59.246 +0900 [INFO] (main): Started Embulk v0.9.15
250
-
251
- 2019-02-20 18:22:59.387 +0900 [INFO] (0001:transaction): Loaded plugin embulk-input-oracle (0.9.3)
252
-
253
- 2019-02-20 18:23:05.468 +0900 [INFO] (0001:transaction): Loaded plugin embulk-output-bigquery (0.4.9)
254
-
255
- 2019-02-20 18:23:05.578 +0900 [INFO] (0001:transaction): Loaded plugin embulk-filter-timestamp_format (0.3.3)
256
-
257
- 2019-02-20 18:23:05.624 +0900 [INFO] (0001:transaction): Connecting to jdbc:oracle:thin:@A0430-02396:1521:ORCL options {oracle.jdbc.ReadTimeout=1800000, user=system, password=***, oracle.net.CONNECT_TIMEOUT=300000}
258
-
259
- 2019-02-20 18:23:06.250 +0900 [INFO] (0001:transaction): Using JDBC Driver 12.1.0.2.0
260
-
261
- 2019-02-20 18:23:06.484 +0900 [INFO] (0001:transaction): Using local thread executor with max_threads=8 / output tasks 4 = input tasks 1 * 4
262
-
263
- 2019-02-20 18:23:06.648 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Get dataset... fluid-emissary-216806:embulk_test
264
-
265
- 2019-02-20 18:23:08.530 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Create table... fluid-emissary-216806:embulk_test.LOAD_TEMP_6d097b7d_3030_43dd_8402_0cafcedabc47_Ora_Bq_TABLE
266
-
267
- 2019-02-20 18:23:09.537 +0900 [INFO] (0001:transaction): {done: 0 / 1, running: 0}
268
-
269
- 2019-02-20 18:23:09.739 +0900 [INFO] (0023:task-0000): Connecting to jdbc:oracle:thin:@A0430-02396:1521:ORCL options {oracle.jdbc.ReadTimeout=1800000, user=system, password=***, oracle.net.CONNECT_TIMEOUT=300000}
270
-
271
- 2019-02-20 18:23:09.926 +0900 [INFO] (0023:task-0000): SQL: SELECT * FROM "T_RS_TRAN" WHERE (("HINBAN" > ?)) ORDER BY "HINBAN"
272
-
273
- 2019-02-20 18:23:09.926 +0900 [INFO] (0023:task-0000): Parameters: ["ZZZZ2W98FZCM"]
274
-
275
- 2019-02-20 18:23:10.238 +0900 [INFO] (0023:task-0000): > 0.30 seconds
276
-
277
- 2019-02-20 18:23:10.254 +0900 [INFO] (0001:transaction): {done: 1 / 1, running: 0}
278
-
279
- 2019-02-20 18:23:10.254 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Get table... fluid-emissary-216806:embulk_test.LOAD_TEMP_6d097b7d_3030_43dd_8402_0cafcedabc47_Ora_Bq_TABLE
280
-
281
- 2019-02-20 18:23:10.924 +0900 [INFO] (0001:transaction): embulk-output-bigquery: transaction_report: {"num_input_rows":0,"num_response_rows":0,"num_output_rows":0,"num_rejected_rows":0}
282
-
283
- 2019-02-20 18:23:10.924 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Copy job starting... job_id:[embulk_copy_job_ab750511-fe9f-4c3a-b37f-0c89a372b7c6] fluid-emissary-216806:embulk_test.LOAD_TEMP_6d097b7d_3030_43dd_8402_0cafcedabc47_Ora_Bq_TABLE => fluid-emissary-216806:embulk_test.Ora_Bq_TABLE
284
-
285
- 2019-02-20 18:23:11.720 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Copy job checking... job_id:[embulk_copy_job_ab750511-fe9f-4c3a-b37f-0c89a372b7c6] elapsed_time:0.0sec status:[RUNNING]
286
-
287
- 2019-02-20 18:23:22.236 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Copy job completed... job_id:[embulk_copy_job_ab750511-fe9f-4c3a-b37f-0c89a372b7c6] elapsed_time:10.516sec status:[DONE]
288
-
289
- 2019-02-20 18:23:22.236 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Copy job response... job_id:[embulk_copy_job_ab750511-fe9f-4c3a-b37f-0c89a372b7c6] response.statistics:{:creation_time=>1550654594994, :start_time=>1550654595117, :end_time=>1550654595199}
290
-
291
- 2019-02-20 18:23:22.251 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Delete table... fluid-emissary-216806:embulk_test.LOAD_TEMP_6d097b7d_3030_43dd_8402_0cafcedabc47_Ora_Bq_TABLE
292
-
293
- 2019-02-20 18:23:22.688 +0900 [INFO] (main): Committed.
294
-
295
- 2019-02-20 18:23:22.688 +0900 [INFO] (main): Next config diff: {"in":{"last_record":["ZZZZ2W98FZCM"]},"out":{}}
296
-
297
-
298
-
299
- C:\Users\yazaki\Desktop\embulk>
300
-
301
-
302
-
303
-
304
-
305
-
306
-
307
- ※※結果として、０レコードがインサートされる事となり、最終レコードのカラム値も「ZZZZ2W98FZCM」のまま
308
-
309
-
221
+ ORDER_NO NUMBER(15)
222
+
223
+ RS_TYPE VARCHAR2(1)
224
+
225
+ FACTORY_CODE VARCHAR2(4)
226
+
227
+ ORG_CODE VARCHAR2(3)
228
+
229
+ TRAN_DATE DATE
310
230
 
311
231
 
312
232
 

1

質問更新

2019/02/21 06:51

投稿

raccoondog
raccoondog

スコア79

test CHANGED
File without changes
test CHANGED
@@ -149,3 +149,165 @@
149
149
 
150
150
 
151
151
  ```
152
+
153
+
154
+
155
+
156
+
157
+ ```ここに言語を入力
158
+
159
+ ■Oracle 初回データ投入後
160
+
161
+
162
+
163
+ SQL> select count(*) from T_RS_TRAN;
164
+
165
+
166
+
167
+ COUNT(*)
168
+
169
+ ----------
170
+
171
+ 999974
172
+
173
+
174
+
175
+
176
+
177
+ ■embulk増分バルクインサート後の最終レコード（hinbanカラム）（diff_OraToBq.yml）
178
+
179
+
180
+
181
+ in:
182
+
183
+ last_record: [ZZZZ2W98FZCM]
184
+
185
+ out: {}
186
+
187
+
188
+
189
+
190
+
191
+ ■Oracle 2回目データ投入後
192
+
193
+ SQL> select count(*) from T_RS_TRAN;
194
+
195
+
196
+
197
+ COUNT(*)
198
+
199
+ ----------
200
+
201
+ 1999955
202
+
203
+
204
+
205
+ ■Oracle データ内のhinbanカラム値を検索し、[ZZZZ2W98FZCM]は１つだけ
206
+
207
+
208
+
209
+ SQL> select hinban from T_RS_TRAN where hinban = 'ZZZZ2W98FZCM';
210
+
211
+
212
+
213
+ HINBAN
214
+
215
+ ------------------------------------------------------------------
216
+
217
+ ZZZZ2W98FZCM
218
+
219
+
220
+
221
+
222
+
223
+ SQL> desc T_RS_TRAN
224
+
225
+ 名前 NULL? 型
226
+
227
+ ----------------------------------------- -------- ----------------------------
228
+
229
+ HINBAN VARCHAR2(40)
230
+
231
+
232
+
233
+
234
+
235
+
236
+
237
+ ■embulkで最終レコードを参照し、増分バルクインサート
238
+
239
+
240
+
241
+ C:\Users\yazaki\Desktop\embulk>embulk-0.9.15.bat run config_OraToBq_incremental.yml.liquid -c diff_OraToBq.yml
242
+
243
+ 2019-02-20 18:22:51.265 +0900: Embulk v0.9.15
244
+
245
+ 2019-02-20 18:22:51.948 +0900 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
246
+
247
+ 2019-02-20 18:22:55.562 +0900 [INFO] (main): Gem's home and path are set by default: "C:\Users\yazaki.embulk\lib\gems"
248
+
249
+ 2019-02-20 18:22:59.246 +0900 [INFO] (main): Started Embulk v0.9.15
250
+
251
+ 2019-02-20 18:22:59.387 +0900 [INFO] (0001:transaction): Loaded plugin embulk-input-oracle (0.9.3)
252
+
253
+ 2019-02-20 18:23:05.468 +0900 [INFO] (0001:transaction): Loaded plugin embulk-output-bigquery (0.4.9)
254
+
255
+ 2019-02-20 18:23:05.578 +0900 [INFO] (0001:transaction): Loaded plugin embulk-filter-timestamp_format (0.3.3)
256
+
257
+ 2019-02-20 18:23:05.624 +0900 [INFO] (0001:transaction): Connecting to jdbc:oracle:thin:@A0430-02396:1521:ORCL options {oracle.jdbc.ReadTimeout=1800000, user=system, password=***, oracle.net.CONNECT_TIMEOUT=300000}
258
+
259
+ 2019-02-20 18:23:06.250 +0900 [INFO] (0001:transaction): Using JDBC Driver 12.1.0.2.0
260
+
261
+ 2019-02-20 18:23:06.484 +0900 [INFO] (0001:transaction): Using local thread executor with max_threads=8 / output tasks 4 = input tasks 1 * 4
262
+
263
+ 2019-02-20 18:23:06.648 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Get dataset... fluid-emissary-216806:embulk_test
264
+
265
+ 2019-02-20 18:23:08.530 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Create table... fluid-emissary-216806:embulk_test.LOAD_TEMP_6d097b7d_3030_43dd_8402_0cafcedabc47_Ora_Bq_TABLE
266
+
267
+ 2019-02-20 18:23:09.537 +0900 [INFO] (0001:transaction): {done: 0 / 1, running: 0}
268
+
269
+ 2019-02-20 18:23:09.739 +0900 [INFO] (0023:task-0000): Connecting to jdbc:oracle:thin:@A0430-02396:1521:ORCL options {oracle.jdbc.ReadTimeout=1800000, user=system, password=***, oracle.net.CONNECT_TIMEOUT=300000}
270
+
271
+ 2019-02-20 18:23:09.926 +0900 [INFO] (0023:task-0000): SQL: SELECT * FROM "T_RS_TRAN" WHERE (("HINBAN" > ?)) ORDER BY "HINBAN"
272
+
273
+ 2019-02-20 18:23:09.926 +0900 [INFO] (0023:task-0000): Parameters: ["ZZZZ2W98FZCM"]
274
+
275
+ 2019-02-20 18:23:10.238 +0900 [INFO] (0023:task-0000): > 0.30 seconds
276
+
277
+ 2019-02-20 18:23:10.254 +0900 [INFO] (0001:transaction): {done: 1 / 1, running: 0}
278
+
279
+ 2019-02-20 18:23:10.254 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Get table... fluid-emissary-216806:embulk_test.LOAD_TEMP_6d097b7d_3030_43dd_8402_0cafcedabc47_Ora_Bq_TABLE
280
+
281
+ 2019-02-20 18:23:10.924 +0900 [INFO] (0001:transaction): embulk-output-bigquery: transaction_report: {"num_input_rows":0,"num_response_rows":0,"num_output_rows":0,"num_rejected_rows":0}
282
+
283
+ 2019-02-20 18:23:10.924 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Copy job starting... job_id:[embulk_copy_job_ab750511-fe9f-4c3a-b37f-0c89a372b7c6] fluid-emissary-216806:embulk_test.LOAD_TEMP_6d097b7d_3030_43dd_8402_0cafcedabc47_Ora_Bq_TABLE => fluid-emissary-216806:embulk_test.Ora_Bq_TABLE
284
+
285
+ 2019-02-20 18:23:11.720 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Copy job checking... job_id:[embulk_copy_job_ab750511-fe9f-4c3a-b37f-0c89a372b7c6] elapsed_time:0.0sec status:[RUNNING]
286
+
287
+ 2019-02-20 18:23:22.236 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Copy job completed... job_id:[embulk_copy_job_ab750511-fe9f-4c3a-b37f-0c89a372b7c6] elapsed_time:10.516sec status:[DONE]
288
+
289
+ 2019-02-20 18:23:22.236 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Copy job response... job_id:[embulk_copy_job_ab750511-fe9f-4c3a-b37f-0c89a372b7c6] response.statistics:{:creation_time=>1550654594994, :start_time=>1550654595117, :end_time=>1550654595199}
290
+
291
+ 2019-02-20 18:23:22.251 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Delete table... fluid-emissary-216806:embulk_test.LOAD_TEMP_6d097b7d_3030_43dd_8402_0cafcedabc47_Ora_Bq_TABLE
292
+
293
+ 2019-02-20 18:23:22.688 +0900 [INFO] (main): Committed.
294
+
295
+ 2019-02-20 18:23:22.688 +0900 [INFO] (main): Next config diff: {"in":{"last_record":["ZZZZ2W98FZCM"]},"out":{}}
296
+
297
+
298
+
299
+ C:\Users\yazaki\Desktop\embulk>
300
+
301
+
302
+
303
+
304
+
305
+
306
+
307
+ ※※結果として、０レコードがインサートされる事となり、最終レコードのカラム値も「ZZZZ2W98FZCM」のまま
308
+
309
+
310
+
311
+
312
+
313
+ ```