あるSQLをクエリエディタで実行したときと、Linuxインスタンス(GCP)のコンソール上で実行した時とで結果が異なる事象が発生
SQL文の構文を変更して実行したが、事象変わらずの状態となります。
■GCP RHELインスタンス ●1度目 [dwhtest01@embulk-bigquery-test autosql]$ 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))" -bash: fluid-emissary-216806.embulk_test.INPUT_TEST_003: command not found -bash: fluid-emissary-216806.embulk_test.INPUT_TEST_001: command not found -bash: fluid-emissary-216806.embulk_test.INPUT_TEST_002: command not found -bash: fluid-emissary-216806.embulk_test.INPUT_TEST_003: command not found Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r1f224426d1a31cf2_0000016850ce483c_1': Encountered " "FROM" "from "" at line 1, column 64. Was expecting: <EOF> ●2度目 [dwhtest01@embulk-bigquery-test autosql]$ 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))" Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r3608d36b1b037c75_0000016850ca97ee_1': Encountered "" at line 1, column 64. [dwhtest01@embulk-bigquery-test autosql]$
※再度select
[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))" Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r7a78024a25a7e177_00000168540dd398_1': Encountered "" at line 1, column 65.
※2019/1/17 ファイル渡し実行結果➀
[dwhtest01@embulk-bigquery-test autosql]$ bq query --flagfile auto.sql Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r6474224b7c9c234c_000001685a1b79b5_1': Encountered " <STRING_LITERAL> "\"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));\" "" at line 1, column 1. Was expecting: <EOF> [dwhtest01@embulk-bigquery-test autosql]$ [dwhtest01@embulk-bigquery-test autosql]$ bq --flagfile auto.sql 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 Run 'bq help' to get help [dwhtest01@embulk-bigquery-test autosql]$ [dwhtest01@embulk-bigquery-test autosql]$ [dwhtest01@embulk-bigquery-test autosql]$ cat auto.sql "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));" [dwhtest01@embulk-bigquery-test autosql]$
※SQL分を変更して実行➀
$ cat auto.sql 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)) $ bq query --flagfile auto.sql Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r24810c52dc220156_000001685a7614c1_1': Encountered " <STRING_LITERAL> "\'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))\' "" at line 1, column 1.Was expecting:<EOF> $ cat auto.sql 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)) $ bq query --flagfile auto.sql Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r773f6197eeba500_000001685a7d92a9_1': Encountered "" at line 1, column 65.
※SQL分を変更して実行➁
[dwhtest01@embulk-bigquery-test autosql]$ bq --flagfile auto.sql 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 Run 'bq help' to get help [dwhtest01@embulk-bigquery-test autosql]$ cat auto.sql 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)) [dwhtest01@embulk-bigquery-test autosql]$ [dwhtest01@embulk-bigquery-test autosql]$ bq --flagfile auto.sql 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 Run 'bq help' to get help [dwhtest01@embulk-bigquery-test autosql]$ cat auto.sql 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))
※bq shellで実行
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)); 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));'] fluid-emissary-216806>
※bq shell queryで実行
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)); Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r3a2890ff8b6aaafb_000001685e8a62ea_1': Encountered "" at line 1, column 65. 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)); Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r4d52c9795e2725bd_000001685e8af20b_1': 1.1 - 1.314: Unrecognized token insert. 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)); Error in query string: Error processing job 'fluid-emissary-216806:bqjob_r2a7db97a8bdbd422_000001685e8b15fd_1': 1.1 - 1.314: Unrecognized token insert.
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2019/01/16 05:06
2019/01/16 23:58
2019/01/17 00:28 編集
2019/01/17 02:43
2019/01/17 03:01 編集
2019/01/17 03:01
2019/01/17 03:01
2019/01/17 03:17
2019/01/17 03:42
2019/01/17 04:43
2019/01/17 05:05 編集
2019/01/17 06:31
2019/01/17 07:44
2019/01/17 07:53
2019/01/17 08:00
2019/01/17 08:14
2019/01/17 08:20
2019/01/17 08:41
2019/01/17 09:24
2019/01/17 23:40
2019/01/18 00:05
2019/01/18 00:31
2019/01/18 00:41 編集
2019/01/18 01:32
2019/01/18 01:54 編集
2019/01/18 02:16
2019/01/18 04:11