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

編集履歴

質問編集履歴

2

質問内容更新

2019/02/21 06:51

投稿

raccoondog
raccoondog

スコア77

title CHANGED
File without changes
body CHANGED
@@ -76,82 +76,42 @@
76
76
  ```
77
77
 
78
78
 
79
+
79
80
  ```ここに言語を入力
80
- ■Oracle 初回データ投入後
81
+ ■embulkによるseed.ymlファイルのpreview結果
81
82
 
82
- SQL> select count(*) from T_RS_TRAN;
83
+ 2019-02-21 15:33:49.668 +0900: Embulk v0.9.15
84
+ 2019-02-21 15:33:50.042 +0900 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
85
+ 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"
86
+ 2019-02-21 15:33:53.222 +0900 [INFO] (main): Started Embulk v0.9.15
87
+ 2019-02-21 15:33:53.284 +0900 [INFO] (0001:preview): Loaded plugin embulk-input-oracle (0.9.3)
88
+ 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}
89
+ 2019-02-21 15:33:53.690 +0900 [INFO] (0001:preview): Using JDBC Driver 12.1.0.2.0
90
+ 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}
91
+ 2019-02-21 15:33:53.939 +0900 [INFO] (0001:preview): SQL: SELECT * FROM "T_RS_TRAN" ORDER BY "ORDER_NO"
92
+ 2019-02-21 15:33:53.955 +0900 [INFO] (0001:preview): > 0.02 seconds
93
+ +-----------------+----------------+---------------------+-----------------+
94
+ | ORDER_NO:double | RS_TYPE:string | FACTORY_CODE:string | ORG_CODE:string |
95
+ +-----------------+----------------+---------------------+-----------------+
96
+ | 2.1002419E7 | 1 | 8000 | MQ9 |
97
+ | 2.100242E7 | 1 | 7500 | TWP |
98
+ | 2.100243E7 | 1 | 7500 | 5BN |
83
99
 
84
- COUNT(*)
85
- ----------
86
- 999974
87
100
 
88
101
 
89
- ■embulk増分バルクインサート後の最終レコード（hinbanカラム）（diff_OraToBq.yml）
102
+ ■Oracleに取り込んだデータ
90
103
 
91
- in:
104
+ "21023317","2","8100","XD9",2018/06/26,"VHP5FUH3F78C",1,"38A8U","06",0,"ZZZ"
92
- last_record: [ZZZZ2W98FZCM]
105
+ "21023321","2","8000","7J7",2018/07/14,"YT98FCU5ZSG5",50,"I0ZSCQX0","30",0,"
93
- out: {}
94
106
 
95
107
 
96
- ■Oracle 2回目データ投入後
97
- SQL> select count(*) from T_RS_TRAN;
98
-
99
- COUNT(*)
100
- ----------
101
- 1999955
102
-
103
- ■Oracle データ内のhinbanカラム値を検索し、[ZZZZ2W98FZCM]は１つだけ
104
-
105
- SQL> select hinban from T_RS_TRAN where hinban = 'ZZZZ2W98FZCM';
106
-
107
- HINBAN
108
- ------------------------------------------------------------------
109
- ZZZZ2W98FZCM
110
-
111
-
112
108
  SQL> desc T_RS_TRAN
113
109
  名前 NULL? 型
114
110
  ----------------------------------------- -------- ----------------------------
111
+ ORDER_NO NUMBER(15)
115
- HINBAN VARCHAR2(40)
112
+ RS_TYPE VARCHAR2(1)
113
+ FACTORY_CODE VARCHAR2(4)
114
+ ORG_CODE VARCHAR2(3)
115
+ TRAN_DATE DATE
116
116
 
117
-
118
-
119
- ■embulkで最終レコードを参照し、増分バルクインサート
120
-
121
- C:\Users\yazaki\Desktop\embulk>embulk-0.9.15.bat run config_OraToBq_incremental.yml.liquid -c diff_OraToBq.yml
122
- 2019-02-20 18:22:51.265 +0900: Embulk v0.9.15
123
- 2019-02-20 18:22:51.948 +0900 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
124
- 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"
125
- 2019-02-20 18:22:59.246 +0900 [INFO] (main): Started Embulk v0.9.15
126
- 2019-02-20 18:22:59.387 +0900 [INFO] (0001:transaction): Loaded plugin embulk-input-oracle (0.9.3)
127
- 2019-02-20 18:23:05.468 +0900 [INFO] (0001:transaction): Loaded plugin embulk-output-bigquery (0.4.9)
128
- 2019-02-20 18:23:05.578 +0900 [INFO] (0001:transaction): Loaded plugin embulk-filter-timestamp_format (0.3.3)
129
- 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}
130
- 2019-02-20 18:23:06.250 +0900 [INFO] (0001:transaction): Using JDBC Driver 12.1.0.2.0
131
- 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
132
- 2019-02-20 18:23:06.648 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Get dataset... fluid-emissary-216806:embulk_test
133
- 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
134
- 2019-02-20 18:23:09.537 +0900 [INFO] (0001:transaction): {done: 0 / 1, running: 0}
135
- 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}
136
- 2019-02-20 18:23:09.926 +0900 [INFO] (0023:task-0000): SQL: SELECT * FROM "T_RS_TRAN" WHERE (("HINBAN" > ?)) ORDER BY "HINBAN"
137
- 2019-02-20 18:23:09.926 +0900 [INFO] (0023:task-0000): Parameters: ["ZZZZ2W98FZCM"]
138
- 2019-02-20 18:23:10.238 +0900 [INFO] (0023:task-0000): > 0.30 seconds
139
- 2019-02-20 18:23:10.254 +0900 [INFO] (0001:transaction): {done: 1 / 1, running: 0}
140
- 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
141
- 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}
142
- 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
143
- 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]
144
- 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]
145
- 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}
146
- 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
147
- 2019-02-20 18:23:22.688 +0900 [INFO] (main): Committed.
148
- 2019-02-20 18:23:22.688 +0900 [INFO] (main): Next config diff: {"in":{"last_record":["ZZZZ2W98FZCM"]},"out":{}}
149
-
150
- C:\Users\yazaki\Desktop\embulk>
151
-
152
-
153
-
154
- ※※結果として、０レコードがインサートされる事となり、最終レコードのカラム値も「ZZZZ2W98FZCM」のまま
155
-
156
-
157
117
  ```

1

質問更新

2019/02/21 06:51

投稿

raccoondog
raccoondog

スコア77

title CHANGED
File without changes
body CHANGED
@@ -73,4 +73,85 @@
73
73
 
74
74
  Error: org.embulk.spi.DataException: Column type 'double' set at incremental_columns option is not supported
75
75
 
76
+ ```
77
+
78
+
79
+ ```ここに言語を入力
80
+ ■Oracle 初回データ投入後
81
+
82
+ SQL> select count(*) from T_RS_TRAN;
83
+
84
+ COUNT(*)
85
+ ----------
86
+ 999974
87
+
88
+
89
+ ■embulk増分バルクインサート後の最終レコード（hinbanカラム）（diff_OraToBq.yml）
90
+
91
+ in:
92
+ last_record: [ZZZZ2W98FZCM]
93
+ out: {}
94
+
95
+
96
+ ■Oracle 2回目データ投入後
97
+ SQL> select count(*) from T_RS_TRAN;
98
+
99
+ COUNT(*)
100
+ ----------
101
+ 1999955
102
+
103
+ ■Oracle データ内のhinbanカラム値を検索し、[ZZZZ2W98FZCM]は１つだけ
104
+
105
+ SQL> select hinban from T_RS_TRAN where hinban = 'ZZZZ2W98FZCM';
106
+
107
+ HINBAN
108
+ ------------------------------------------------------------------
109
+ ZZZZ2W98FZCM
110
+
111
+
112
+ SQL> desc T_RS_TRAN
113
+ 名前 NULL? 型
114
+ ----------------------------------------- -------- ----------------------------
115
+ HINBAN VARCHAR2(40)
116
+
117
+
118
+
119
+ ■embulkで最終レコードを参照し、増分バルクインサート
120
+
121
+ C:\Users\yazaki\Desktop\embulk>embulk-0.9.15.bat run config_OraToBq_incremental.yml.liquid -c diff_OraToBq.yml
122
+ 2019-02-20 18:22:51.265 +0900: Embulk v0.9.15
123
+ 2019-02-20 18:22:51.948 +0900 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
124
+ 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"
125
+ 2019-02-20 18:22:59.246 +0900 [INFO] (main): Started Embulk v0.9.15
126
+ 2019-02-20 18:22:59.387 +0900 [INFO] (0001:transaction): Loaded plugin embulk-input-oracle (0.9.3)
127
+ 2019-02-20 18:23:05.468 +0900 [INFO] (0001:transaction): Loaded plugin embulk-output-bigquery (0.4.9)
128
+ 2019-02-20 18:23:05.578 +0900 [INFO] (0001:transaction): Loaded plugin embulk-filter-timestamp_format (0.3.3)
129
+ 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}
130
+ 2019-02-20 18:23:06.250 +0900 [INFO] (0001:transaction): Using JDBC Driver 12.1.0.2.0
131
+ 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
132
+ 2019-02-20 18:23:06.648 +0900 [INFO] (0001:transaction): embulk-output-bigquery: Get dataset... fluid-emissary-216806:embulk_test
133
+ 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
134
+ 2019-02-20 18:23:09.537 +0900 [INFO] (0001:transaction): {done: 0 / 1, running: 0}
135
+ 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}
136
+ 2019-02-20 18:23:09.926 +0900 [INFO] (0023:task-0000): SQL: SELECT * FROM "T_RS_TRAN" WHERE (("HINBAN" > ?)) ORDER BY "HINBAN"
137
+ 2019-02-20 18:23:09.926 +0900 [INFO] (0023:task-0000): Parameters: ["ZZZZ2W98FZCM"]
138
+ 2019-02-20 18:23:10.238 +0900 [INFO] (0023:task-0000): > 0.30 seconds
139
+ 2019-02-20 18:23:10.254 +0900 [INFO] (0001:transaction): {done: 1 / 1, running: 0}
140
+ 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
141
+ 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}
142
+ 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
143
+ 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]
144
+ 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]
145
+ 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}
146
+ 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
147
+ 2019-02-20 18:23:22.688 +0900 [INFO] (main): Committed.
148
+ 2019-02-20 18:23:22.688 +0900 [INFO] (main): Next config diff: {"in":{"last_record":["ZZZZ2W98FZCM"]},"out":{}}
149
+
150
+ C:\Users\yazaki\Desktop\embulk>
151
+
152
+
153
+
154
+ ※※結果として、０レコードがインサートされる事となり、最終レコードのカラム値も「ZZZZ2W98FZCM」のまま
155
+
156
+
76
157
  ```