teratail
回答率
85.48%
質問するログイン新規登録
トップに関する質問bigqueryテーブルのデータ型変換につきまして

編集履歴

質問編集履歴

5

質問更新

2019/04/16 08:02

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
@@ -156,16 +156,248 @@
156
156
 
157
157
 
158
158
 
159
- oracle⇒embulk⇒embulk_plugin⇒bigquery【処理結果】
160
-
161
-
162
-
163
- NUMBER⇒double※embulkにより自動変換⇒float⇒float【●】
164
-
165
- NUMBER⇒double※embulkにより自動変換⇒float⇒NUMERIC【×】
166
-
167
- NUMBER⇒long※embulkの定義で強制変換⇒integer⇒NUMERIC【×】
168
-
169
- NUMBER⇒string※embulkの定義で強制変換⇒string⇒NUMERIC【×】
159
+ ●embulk定義
160
+
161
+ in:
162
+
163
+ type: oracle
164
+
165
+ path_prefix: null
166
+
167
+ driver_path: /opt/oracle/product/12.1.0/db/jdbc/lib/ojdbc7.jar
168
+
169
+ host: srdcs04
170
+
171
+ user: srdcs
172
+
173
+ password: srdcs
174
+
175
+ database: srdcsc
176
+
177
+ column_options:
178
+
179
+ ORDER_NO: { value_type: string }
180
+
181
+ query: |
182
+
183
+ select
184
+
185
+ trs.ORDER_NO,
186
+
187
+ trs.RS_TYPE,
188
+
189
+ trs.FACTORY_CODE,
190
+
191
+ trs.ORG_CODE,
192
+
193
+ trs.TRAN_DATE,
194
+
195
+ trs.HINBAN,
196
+
197
+ trs.QTY,
198
+
199
+ trs.SLIP_NO,
200
+
201
+ trs.ITEM_NO,
202
+
203
+ trs.DIVISION_NO,
204
+
205
+ trs.FROM_FACTORY_CODE,
206
+
207
+ trs.FROM_ORG_CODE,
208
+
209
+ trs.FROM_SUBINV_CODE,
210
+
211
+ trs.FROM_VALUE_CODE,
212
+
213
+ trs.FROM_OPERATOR_NAME,
214
+
215
+ trs.TO_FACTORY_CODE,
216
+
217
+ trs.TO_ORG_CODE,
218
+
219
+ trs.TO_SUBINV_CODE,
220
+
221
+ trs.TO_VALUE_CODE,
222
+
223
+ trs.TO_OPERATOR_NAME,
224
+
225
+ trs.SUB_SYSTEM_TYPE1,
226
+
227
+ trs.CANCEL_FLAG,
228
+
229
+ trs.ACT_CREATION_DATE,
230
+
231
+ trs.WH_ACT_CREATION_DATE,
232
+
233
+ trs.JOB_CODE,
234
+
235
+ trs.UNIT_PRICE,
236
+
237
+ trs.ERROR_STATUS,
238
+
239
+ trs.DESCRIPTION,
240
+
241
+ trs.LOCATION,
242
+
243
+ trs.REVISION_NO,
244
+
245
+ trs.OPE_CODE,
246
+
247
+ trs.BOX_KIND,
248
+
249
+ trs.DEMAND_NO,
250
+
251
+ trs.SUB_NO,
252
+
253
+ trs.TRAFFIC_NO,
254
+
255
+ trs.IF_FLAG,
256
+
257
+ trs.SELL_BUY_DATA_FLAG,
258
+
259
+ trs.SOURCE_ORDER_NO,
260
+
261
+ trs.FT_DEFINE_ID,
262
+
263
+ trs.FROM_SELL_BUY_ID,
264
+
265
+ trs.TO_SELL_BUY_ID,
266
+
267
+ trs.CREATION_DATE,
268
+
269
+ trs.CREATED_BY,
270
+
271
+ trs.LAST_UPDATE_DATE,
272
+
273
+ trs.LAST_UPDATED_BY,
274
+
275
+ trs.UPDATE_REQUEST_ID,
276
+
277
+ trs.TIME_STAMP,
278
+
279
+ trs.INV_UPDATE_FLAG,
280
+
281
+ trs.ISSUE_SEQ,
282
+
283
+ trs.SOUMEI_ID,
284
+
285
+ trs.GCPCS_IF_FLAG,
286
+
287
+ trs.GCPCS_IF_CREATION_DATE,
288
+
289
+ trs.RECIEPT_CODE,
290
+
291
+ trs.DELIV_BIN_NO,
292
+
293
+ trs.DELIV_NO,
294
+
295
+ trs.RECV_BIN_NO,
296
+
297
+ trs.SUB_SYSTEM_TYPE2,
298
+
299
+ trs.SHIP_DATE,
300
+
301
+ trs.C_NO,
302
+
303
+ trs.T_NO,
304
+
305
+ trs.RECV_OFFICE_CODE,
306
+
307
+ trs.SR_IF_FLAG,
308
+
309
+ trs.SR_IF_CREATION_DATE,
310
+
311
+ trs.PS_IF_FLAG,
312
+
313
+ trs.PS_IF_CREATION_DATE,
314
+
315
+ trs.CUSTOMER_CODE,
316
+
317
+ trs.KS_type,
318
+
319
+ trs.SUPPLY_SECTION,
320
+
321
+ trs.MANAGE_NO,
322
+
323
+ trs.SEND_OFFICE_CODE,
324
+
325
+ trs.SOUMEI_SLIP_NO,
326
+
327
+ trs.SOUMEI_ITEM_NO,
328
+
329
+ trs.PS_YAZAKI_HINBAN,
330
+
331
+ trs.INVOICE_CASE_INFO_NO,
332
+
333
+ trs.GYK_SITE_CODE,
334
+
335
+ trs.CASE_NO,
336
+
337
+ trs.CASE_SUB_NO,
338
+
339
+ trs.FROM_LOCATION,
340
+
341
+ trs.TO_LOCATION,
342
+
343
+ trs.GYK_TRAN_type,
344
+
345
+ trs.INV_INFO_UPDATE_FLAG,
346
+
347
+ (select code_description from m_code where code_type = 'rs_type' and code = trs.rs_type) rs_name,
348
+
349
+ (select org_name from m_org where org_code = trs.org_code ) org_name,
350
+
351
+ (select factory_name from m_factory where factory_code = trs.from_factory_code ) from_factory_name,
352
+
353
+ (select org_name from m_org where org_code = trs.from_org_code ) from_org_name,
354
+
355
+ (select subinv_name from m_subinv where org_code = trs.from_org_code and subinv_code = trs.from_subinv_code) from_subinv_name,
356
+
357
+ (select factory_name from m_factory where factory_code = trs.to_factory_code ) to_factory_name,
358
+
359
+ (select org_name from m_org where org_code = trs.to_org_code ) to_org_name,
360
+
361
+ (select subinv_name from m_subinv where org_code = trs.to_org_code and subinv_code = trs.to_subinv_code) to_subinv_name,
362
+
363
+ (select ope_code_name from m_ope_code where factory_code = trs.factory_code and ope_code = trs.ope_code) ope_code_name,
364
+
365
+ (select sub_system_name1 from m_sub_system_type1 where sub_system_type1 = trs.sub_system_type1) sub_system_name1,
366
+
367
+ (select code_description from m_code where code_type= 'cancel_flag' and code= trs.cancel_flag) cancel_name,
368
+
369
+ (select sub_system_name2 from m_sub_system_type2 where sub_system_type1 = trs.sub_system_type1 and sub_system_type2 = trs.sub_system_type2) sub_system_name2
370
+
371
+ from
372
+
373
+ T_RS_TRAN trs
374
+
375
+ where
376
+
377
+ IF_FLAG = '1' and TO_DATE(TRAN_DATE) >= TO_DATE('20190226','yyyyMMdd') and TO_DATE(TRAN_DATE) <= TO_DATE('20190227','yyyyMMdd')
378
+
379
+
380
+
381
+ out: {
382
+
383
+ type: bigquery,
384
+
385
+ auth_method: json_key,
386
+
387
+ json_keyfile: '/home1/bigquery/conf/My First Project-6041ffecfba7.json',
388
+
389
+ project: fluid-emissary-216806,
390
+
391
+ dataset: embulk_test,
392
+
393
+ auto_create_table: true,
394
+
395
+ table: T_RS_TRAN,
396
+
397
+ open_read_timeout_sec: 360000,
398
+
399
+ send_timeout_sec: 360000,
400
+
401
+ read_timeout_sec: 360000}
170
402
 
171
403
  ```

4

状況更新

2019/04/16 08:01

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
@@ -46,6 +46,116 @@
46
46
 
47
47
  ```ここに言語を入力
48
48
 
49
+ 環境：oracle 12c、embulk 0.9.15
50
+
51
+
52
+
53
+ ※Oracle定義
54
+
55
+
56
+
57
+ SQL> desc T_RS_TRAN
58
+
59
+ 名前 NULL? 型
60
+
61
+ ----------------------------------------- -------- ----------------------------
62
+
63
+ ORDER_NO NOT NULL NUMBER(15)
64
+
65
+ RS_TYPE NOT NULL VARCHAR2(1)
66
+
67
+ FACTORY_CODE NOT NULL VARCHAR2(4)
68
+
69
+ ORG_CODE NOT NULL VARCHAR2(3)
70
+
71
+ TRAN_DATE NOT NULL DATE
72
+
73
+ HINBAN NOT NULL VARCHAR2(40)
74
+
75
+ QTY NOT NULL NUMBER(11,2)
76
+
77
+ SLIP_NO NOT NULL VARCHAR2(10)
78
+
79
+ ITEM_NO NOT NULL VARCHAR2(2)
80
+
81
+ DIVISION_NO NUMBER(1)
82
+
83
+ ●●
84
+
85
+ ●●
86
+
87
+ ●●
88
+
89
+ INV_INFO_UPDATE_FLAG VARCHAR2(1)
90
+
91
+
92
+
93
+ ※実行ログ
94
+
95
+ 2019-04-16 16:47:24.274 +0900 [ERROR] (0001:transaction): embulk-output-bigquery: failed during waiting a Copy job, get_job(yzk-gcp-project, embulk_copy_job_ec19947a-757f-4119-9620-7ccdeb023777), errors:[{:reason=>"invalid", :message=>"Provided Schema does not match Table yzk-gcp-project:srdcs_data.T_RS_TRAN_20190416. Field ORDER_NO has changed type from NUMERIC to STRING"}]
96
+
97
+ 2019-04-16 16:47:24.276 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Delete table... yzk-gcp-project:srdcs_data.LOAD_TEMP_eb1cab64_4892_425b_bf29_d279cc655440_T_RS_TRAN_20190416
98
+
99
+ 2019-04-16 16:47:24.590 +0900 [INFO] (0001:transaction): embulk-output-bigquery: delete /var/tmp/embulk_output_bigquery_20190416-15487-1vrnvze.15487.2004.csv
100
+
101
+ 2019-04-16 16:47:24.590 +0900 [INFO] (0001:transaction): embulk-output-bigquery: delete /var/tmp/embulk_output_bigquery_20190416-15487-1vrnvze.15487.2006.csv
102
+
103
+ 2019-04-16 16:47:24.591 +0900 [INFO] (0001:transaction): embulk-output-bigquery: delete /var/tmp/embulk_output_bigquery_20190416-15487-1vrnvze.15487.2008.csv
104
+
105
+ 2019-04-16 16:47:24.591 +0900 [INFO] (0001:transaction): embulk-output-bigquery: delete /var/tmp/embulk_output_bigquery_20190416-15487-1vrnvze.15487.2010.csv
106
+
107
+ 2019-04-16 16:47:24.591 +0900 [INFO] (0001:transaction): embulk-output-bigquery: delete /var/tmp/embulk_output_bigquery_20190416-15487-1vrnvze.15487.2012.csv
108
+
109
+ org.embulk.exec.PartialExecutionException: org.jruby.exceptions.RaiseException: (Error) failed during waiting a Copy job, get_job(yzk-gcp-project, embulk_copy_job_ec19947a-757f-4119-9620-7ccdeb023777), errors:[{:reason=>"invalid", :message=>"Provided Schema does not match Table yzk-gcp-project:srdcs_data.T_RS_TRAN_20190416. Field ORDER_NO has changed type from NUMERIC to STRING"}]
110
+
111
+ at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:340)
112
+
113
+ at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:566)
114
+
115
+ at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:35)
116
+
117
+ at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
118
+
119
+ at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
120
+
121
+ at org.embulk.spi.Exec.doWith(Exec.java:22)
122
+
123
+ at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
124
+
125
+ at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:178)
126
+
127
+ at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:292)
128
+
129
+ at org.embulk.EmbulkRunner.run(EmbulkRunner.java:156)
130
+
131
+ at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:436)
132
+
133
+ at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:91)
134
+
135
+ at org.embulk.cli.Main.main(Main.java:64)
136
+
137
+ Caused by: org.jruby.exceptions.RaiseException: (Error) failed during waiting a Copy job, get_job(yzk-gcp-project, embulk_copy_job_ec19947a-757f-4119-9620-7ccdeb023777), errors:[{:reason=>"invalid", :message=>"Provided Schema does not match Table yzk-gcp-project:srdcs_data.T_RS_TRAN_20190416. Field ORDER_NO has changed type from NUMERIC to STRING"}]
138
+
139
+ at RUBY.wait_load(/export/home/srdcs/.embulk/lib/gems/gems/embulk-output-bigquery-0.4.9/lib/embulk/output/bigquery/bigquery_client.rb:346)
140
+
141
+ at RUBY.block in copy(/export/home/srdcs/.embulk/lib/gems/gems/embulk-output-bigquery-0.4.9/lib/embulk/output/bigquery/bigquery_client.rb:289)
142
+
143
+ 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)
144
+
145
+ at RUBY.copy(/export/home/srdcs/.embulk/lib/gems/gems/embulk-output-bigquery-0.4.9/lib/embulk/output/bigquery/bigquery_client.rb:249)
146
+
147
+ at RUBY.transaction(/export/home/srdcs/.embulk/lib/gems/gems/embulk-output-bigquery-0.4.9/lib/embulk/output/bigquery.rb:407)
148
+
149
+ at RUBY.transaction(uri:classloader:/gems/embulk-0.9.15-java/lib/embulk/output_plugin.rb:64)
150
+
151
+
152
+
153
+ Error: org.jruby.exceptions.RaiseException: (Error) failed during waiting a Copy job, get_job(yzk-gcp-project, embulk_copy_job_ec19947a-757f-4119-9620-7ccdeb023777), errors:[{:reason=>"invalid", :message=>"Provided Schema does not match Table yzk-gcp-project:srdcs_data.T_RS_TRAN_20190416. Field ORDER_NO has changed type from NUMERIC to STRING"}]
154
+
155
+
156
+
157
+
158
+
49
159
  oracle⇒embulk⇒embulk_plugin⇒bigquery【処理結果】
50
160
 
51
161
 

3

質問更新

2019/04/16 07:47

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
@@ -39,3 +39,23 @@
39
39
 
40
40
 
41
41
  ※OracleのVIEWは使用せず、対応したいです。
42
+
43
+
44
+
45
+ ※2019/4/16
46
+
47
+ ```ここに言語を入力
48
+
49
+ oracle⇒embulk⇒embulk_plugin⇒bigquery【処理結果】
50
+
51
+
52
+
53
+ NUMBER⇒double※embulkにより自動変換⇒float⇒float【●】
54
+
55
+ NUMBER⇒double※embulkにより自動変換⇒float⇒NUMERIC【×】
56
+
57
+ NUMBER⇒long※embulkの定義で強制変換⇒integer⇒NUMERIC【×】
58
+
59
+ NUMBER⇒string※embulkの定義で強制変換⇒string⇒NUMERIC【×】
60
+
61
+ ```

2

質問更新

2019/04/16 07:41

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
@@ -35,3 +35,7 @@
35
35
 
36
36
 
37
37
  ```
38
+
39
+
40
+
41
+ ※OracleのVIEWは使用せず、対応したいです。

1

質問内容更新

2019/04/10 00:40

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
File without changes