teratail
回答率
85.48%
質問するログイン新規登録
トップに関する質問クエリエディタでの実行時、Linuxインスタンスでの実行時とで結果が異なる

編集履歴

質問編集履歴

7

状況更新

2019/01/18 01:30

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
@@ -1,32 +1,10 @@
1
1
  あるSQLをクエリエディタで実行したときと、Linuxインスタンス(GCP)のコンソール上で実行した時とで結果が異なる事象が発生
2
2
 
3
-
4
-
5
3
  SQL文の構文を変更して実行したが、事象変わらずの状態となります。
6
4
 
7
5
 
8
6
 
9
-
10
-
11
- ```
7
+ ```
12
-
13
- ■クエリエディタ
14
-
15
-
16
-
17
- insert into `fluid-emissary-216806.embulk_test.INPUT_TEST_003` (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9)
18
-
19
- select * from `fluid-emissary-216806.embulk_test.INPUT_TEST_001`
20
-
21
- where Num In
22
-
23
- (select Num from `fluid-emissary-216806.embulk_test.INPUT_TEST_002` A
24
-
25
- where not exists(select 1 from `fluid-emissary-216806.embulk_test.INPUT_TEST_003` B where A.NUM=B.NUM));
26
-
27
-
28
-
29
-
30
8
 
31
9
  ■GCP RHELインスタンス
32
10
 
@@ -68,36 +46,6 @@
68
46
 
69
47
 
70
48
 
71
- ※テーブルのselect
72
-
73
- ```
74
-
75
- [dwhtest01@embulk-bigquery-test ~]$ bq query "select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001]"
76
-
77
- Waiting on bqjob_r1568021d5b6d8862_00000168540cba87_1 ... (0s) Current status: DONE
78
-
79
- +---------+-------------+----------+----------+---------------------+---------------------+---------------------+---------------------+
80
-
81
- | BANGOU | NUM | STR | VARSTR | DT | TIME0 | TIME6 | TIME9 |
82
-
83
- +---------+-------------+----------+----------+---------------------+---------------------+---------------------+---------------------+
84
-
85
- | 27419.0 | 4.8636074E7 | OFWZPCXD | IYVBPMGG | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 |
86
-
87
- | 27420.0 | 2.3098889E7 | WSDZJHFM | GAUTNACL | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 |
88
-
89
- | 27421.0 | 8.076247E7 | SKFKQTUK | HVIOUZMU | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 |
90
-
91
- | 27422.0 | 5.1477349E7 | XJHSAMXT | IQXFUTNL | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 |
92
-
93
- | 27423.0 | 8.0748174E7 | XWZAHZYH | XCFZBNRD | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 |
94
-
95
-
96
-
97
- ```
98
-
99
-
100
-
101
49
  ※再度select
102
50
 
103
51
  ```
@@ -259,3 +207,25 @@
259
207
  fluid-emissary-216806>
260
208
 
261
209
  ```
210
+
211
+ ※bq shell queryで実行
212
+
213
+ ```ここに言語を入力
214
+
215
+ fluid-emissary-216806> query insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003] (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) select (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM));
216
+
217
+ Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r3a2890ff8b6aaafb_000001685e8a62ea_1': Encountered "" at line 1, column 65.
218
+
219
+
220
+
221
+ fluid-emissary-216806> query insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003] select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM));
222
+
223
+ Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r4d52c9795e2725bd_000001685e8af20b_1': 1.1 - 1.314: Unrecognized token insert.
224
+
225
+
226
+
227
+ fluid-emissary-216806> query insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003] select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM));
228
+
229
+ Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r2a7db97a8bdbd422_000001685e8b15fd_1': 1.1 - 1.314: Unrecognized token insert.
230
+
231
+ ```

6

質問更新

2019/01/18 01:30

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
@@ -245,3 +245,17 @@
245
245
 
246
246
 
247
247
  ```
248
+
249
+
250
+
251
+ ※bq shellで実行
252
+
253
+ ```ここに言語を入力
254
+
255
+ fluid-emissary-216806> insert into `fluid-emissary-216806.embulk_test.INPUT_TEST_003` (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) select * from `fluid-emissary-216806.embulk_test.INPUT_TEST_001` where Num In (select Num from `fluid-emissary-216806.embulk_test.INPUT_TEST_002` A where not exists(select 1 from `fluid-emissary-216806.embulk_test.INPUT_TEST_003` B where A.NUM=B.NUM));
256
+
257
+ Too many positional args, still have ['(ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9)', 'select', '*', 'from', '`fluid-emissary-216806.embulk_test.INPUT_TEST_001`', 'where', 'Num', 'In', '(select', 'Num', 'from', '`fluid-emissary-216806.embulk_test.INPUT_TEST_002`', 'A', 'where', 'not', 'exists(select', '1', 'from', '`fluid-emissary-216806.embulk_test.INPUT_TEST_003`', 'B', 'where', 'A.NUM=B.NUM));']
258
+
259
+ fluid-emissary-216806>
260
+
261
+ ```

5

質問内容の更新

2019/01/17 23:38

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
@@ -158,7 +158,7 @@
158
158
 
159
159
  ```
160
160
 
161
- ※SQL分を変更して実行
161
+ ※SQL分を変更して実行➀
162
162
 
163
163
  ```
164
164
 
@@ -207,3 +207,41 @@
207
207
 
208
208
 
209
209
  ```
210
+
211
+
212
+
213
+ ※SQL分を変更して実行➁
214
+
215
+ ```
216
+
217
+ [dwhtest01@embulk-bigquery-test autosql]$ bq --flagfile auto.sql
218
+
219
+ FATAL Command 'insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003] (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) select (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM))' unknown
220
+
221
+ Run 'bq help' to get help
222
+
223
+
224
+
225
+ [dwhtest01@embulk-bigquery-test autosql]$ cat auto.sql
226
+
227
+ insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003] (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) select (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM))
228
+
229
+ [dwhtest01@embulk-bigquery-test autosql]$
230
+
231
+
232
+
233
+
234
+
235
+ [dwhtest01@embulk-bigquery-test autosql]$ bq --flagfile auto.sql
236
+
237
+ FATAL Command 'insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003] select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM))' unknown
238
+
239
+ Run 'bq help' to get help
240
+
241
+ [dwhtest01@embulk-bigquery-test autosql]$ cat auto.sql
242
+
243
+ insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003] select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM))
244
+
245
+
246
+
247
+ ```

4

状況更新

2019/01/17 07:51

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
@@ -157,3 +157,53 @@
157
157
  [dwhtest01@embulk-bigquery-test autosql]$
158
158
 
159
159
  ```
160
+
161
+ ※SQL分を変更して実行
162
+
163
+ ```
164
+
165
+ $ cat auto.sql
166
+
167
+ insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003]
168
+
169
+ select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001]
170
+
171
+ where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A
172
+
173
+ where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM))
174
+
175
+
176
+
177
+ $ bq query --flagfile auto.sql
178
+
179
+ Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r24810c52dc220156_000001685a7614c1_1': Encountered " <STRING_LITERAL> "\'insert into
180
+
181
+ [fluid-emissary-216806.embulk_test.INPUT_TEST_003] select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num In (select Num from
182
+
183
+ [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM))\' "" at line 1, column 1.Was expecting:<EOF>
184
+
185
+
186
+
187
+
188
+
189
+ $ cat auto.sql
190
+
191
+ insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003] (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9)
192
+
193
+ select (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) from [fluid-emissary-216806.embulk_test.INPUT_TEST_001]
194
+
195
+ where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A
196
+
197
+ where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM))
198
+
199
+
200
+
201
+ $ bq query --flagfile auto.sql
202
+
203
+ Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r773f6197eeba500_000001685a7d92a9_1': Encountered "" at line 1, column 65.
204
+
205
+
206
+
207
+
208
+
209
+ ```

3

質問内容の更新

2019/01/17 06:29

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
@@ -111,3 +111,49 @@
111
111
 
112
112
 
113
113
  ```
114
+
115
+ ※2019/1/17 ファイル渡し実行結果➀
116
+
117
+ ```
118
+
119
+ [dwhtest01@embulk-bigquery-test autosql]$ bq query --flagfile auto.sql
120
+
121
+ Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r6474224b7c9c234c_000001685a1b79b5_1': Encountered " <STRING_LITERAL> "\"insert into
122
+
123
+ [fluid-emissary-216806.embulk_test.INPUT_TEST_003] (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num
124
+
125
+ In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where
126
+
127
+ A.NUM=B.NUM));\" "" at line 1, column 1.
128
+
129
+ Was expecting:
130
+
131
+ <EOF>
132
+
133
+
134
+
135
+ [dwhtest01@embulk-bigquery-test autosql]$
136
+
137
+ [dwhtest01@embulk-bigquery-test autosql]$ bq --flagfile auto.sql
138
+
139
+ FATAL Command '"insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003] (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM));"' unknown
140
+
141
+ Run 'bq help' to get help
142
+
143
+ [dwhtest01@embulk-bigquery-test autosql]$
144
+
145
+ [dwhtest01@embulk-bigquery-test autosql]$
146
+
147
+
148
+
149
+
150
+
151
+
152
+
153
+ [dwhtest01@embulk-bigquery-test autosql]$ cat auto.sql
154
+
155
+ "insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003] (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM));"
156
+
157
+ [dwhtest01@embulk-bigquery-test autosql]$
158
+
159
+ ```

2

質問内容の更新

2019/01/17 04:42

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
@@ -95,3 +95,19 @@
95
95
 
96
96
 
97
97
  ```
98
+
99
+
100
+
101
+ ※再度select
102
+
103
+ ```
104
+
105
+ [dwhtest01@embulk-bigquery-test ~]$ bq query "insert into [fluid-emissary-216806.embulk_test.INPUT_TEST_003] (ID,NUM,STR,VARSTR,DT,TIME0,TIME6,TIME9) select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001] where Num In (select Num from [fluid-emissary-216806.embulk_test.INPUT_TEST_002] A where not exists(select 1 from [fluid-emissary-216806.embulk_test.INPUT_TEST_003] B where A.NUM=B.NUM))"
106
+
107
+
108
+
109
+ Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r7a78024a25a7e177_00000168540dd398_1': Encountered "" at line 1, column 65.
110
+
111
+
112
+
113
+ ```

1

質問更新

2019/01/16 05:08

投稿

raccoondog
raccoondog

スコア77

test CHANGED
File without changes
test CHANGED
@@ -65,3 +65,33 @@
65
65
 
66
66
 
67
67
  ```
68
+
69
+
70
+
71
+ ※テーブルのselect
72
+
73
+ ```
74
+
75
+ [dwhtest01@embulk-bigquery-test ~]$ bq query "select * from [fluid-emissary-216806.embulk_test.INPUT_TEST_001]"
76
+
77
+ Waiting on bqjob_r1568021d5b6d8862_00000168540cba87_1 ... (0s) Current status: DONE
78
+
79
+ +---------+-------------+----------+----------+---------------------+---------------------+---------------------+---------------------+
80
+
81
+ | BANGOU | NUM | STR | VARSTR | DT | TIME0 | TIME6 | TIME9 |
82
+
83
+ +---------+-------------+----------+----------+---------------------+---------------------+---------------------+---------------------+
84
+
85
+ | 27419.0 | 4.8636074E7 | OFWZPCXD | IYVBPMGG | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 |
86
+
87
+ | 27420.0 | 2.3098889E7 | WSDZJHFM | GAUTNACL | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 |
88
+
89
+ | 27421.0 | 8.076247E7 | SKFKQTUK | HVIOUZMU | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 |
90
+
91
+ | 27422.0 | 5.1477349E7 | XJHSAMXT | IQXFUTNL | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 |
92
+
93
+ | 27423.0 | 8.0748174E7 | XWZAHZYH | XCFZBNRD | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 | 2018-12-25 05:59:08 |
94
+
95
+
96
+
97
+ ```