embulkにおける、""(ダブルクォーテーション)つきのCSVファイルの取込みについて

Question

csvファイルに""つきの場合、embulkで処理をする際にレコードスキップされます。
quotaオプションで""は定義しているのですが、スキップされてしまいます。

※環境：Oracle 12c、embulk 0.9.15

※embulk定義
srdcs@SSVR0224:/home1/bigquery/conf$ cat config_T_RS_TRAN.yml
in:
  type: file
  path_prefix: /home1/bigquery/out
  parser:
    type: csv
    delimiter: ','
    skip_header_line: false
    charset: Shift-JIS
    newline: CRLF
    quote: '"'
    escape: '\'
    trim_if_not_quoted: false
    skip_header_lines: 0
    allow_extra_columns: false
    allow_optional_columns: true
    port: 1533
    columns:
    - {name: ORDER_NO, type: long}
    - {name: RS_TYPE, type: string}
    - {name: FACTORY_CODE, type: string}
    - {name: ORG_CODE, type: string}
    - {name: TRAN_DATE, type: string}
    - {name: HINBAN, type: string}
    中略
    - {name: CANCEL_NAME, type: string}
    - {name: SUB_SYSTEM_NAME2, type: string}
filters:
  - type: timestamp_format
    default_to_timezone: "Asia/Tokyo"
    default_from_timestamp_format: ["%Y/%m/%d", "%Y/%m/%d %H:%M:%S"]
    columns:
    - {name: TRAN_DATE, type: timestamp, format: '%Y/%m/%d'}
    - {name: ACT_CREATION_DATE, type: timestamp, format: '%Y/%m/%d %H:%M:%S'}
    - {name: WH_ACT_CREATION_DATE, type: timestamp, format: '%Y/%m/%d %H:%M:%S'}
    - {name: TIME_STAMP, type: timestamp, format: '%Y/%m/%d %H:%M:%S'}
out: 
   location: asia-northeast1
   type: bigquery
   auth_method: json_key,
   json_keyfile: '/home1/bigquery/conf/YZK GCP Project-8bc479ac2a95.json'
   project: yzk-gcp-project
   dataset: srdcs_data
   auto_create_table: true
   table: T_RS_TRAN_SSVR0224_FULL
   open_read_timeout_sec: 360000
   send_timeout_sec: 360000
   read_timeout_sec: 360000


※Oracle定義
SQL> desc T_RS_TRAN
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_NO                                  NOT NULL NUMBER(15)
 RS_TYPE                                            VARCHAR2(1)
 FACTORY_CODE                                       VARCHAR2(4)
 ORG_CODE                                           VARCHAR2(3)
 TRAN_DATE                                          DATE
 HINBAN                                             VARCHAR2(40)
 QTY                                                NUMBER(11,2)
 SLIP_NO                                            VARCHAR2(10)
 ITEM_NO                                            VARCHAR2(2)
    中略
 GYK_TRAN_TYPE                                      VARCHAR2(2)
 INV_INFO_UPDATE_FLAG                               VARCHAR2(1)

SQL>

※csvファイル
"9649379","1","7200","S7A","2019/03/20","TEST","1","TEST0320","01","0","YZK","YZK","3200","","","7200","S7A","MBC1001","","","G","1","2019/04/18 09:48:14","2019/04/18 09:48:14","","","0","","","","","","","","","1","0","9649379","1200002300135","","","2019/04/18 09:48:14","GYK-YZ000","2019/04/18 09:48:14","GYK-YZ000","7043553","2019/04/18 09:48:14.491","0","","","","","","","","","2","","","","","","","","","","","","","","","","","13877897","R01","8","0","","*-1","11","","受入","裾野製作所","ADS豊田(逆輸入)","WH主管工場以外","WH主管工場以外","TAP","裾野製作所","ADS豊田(逆輸入)","ADS豊田(逆輸入) 検前ストア","","逆輸入","訂正","検前受入"

※実行ログ

$embulk preview config_T_RS_TRAN01.yml
2019-04-18 10:13:22.944 +0900: Embulk v0.9.15
2019-04-18 10:13:23.180 +0900 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
2019-04-18 10:13:24.611 +0900 [INFO] (main): Gem's home and path are set by default: "/export/home/srdcs/.embulk/lib/gems"
2019-04-18 10:13:25.037 +0900 [INFO] (main): Started Embulk v0.9.15
2019-04-18 10:13:25.080 +0900 [INFO] (0001:preview): Loaded plugin embulk-filter-timestamp_format (0.3.3)
2019-04-18 10:13:25.098 +0900 [INFO] (0001:preview): Listing local files at directory '/home1/bigquery/out' filtering filename by prefix ''
2019-04-18 10:13:25.099 +0900 [INFO] (0001:preview): "follow_symlinks" is set false. Note that symbolic links to directories are skipped.
2019-04-18 10:13:25.099 +0900 [INFO] (0001:preview): Loading files [/home1/bigquery/out/GCP_CSV_IF20190418095020_ENB]
2019-04-18 10:13:25.104 +0900 [INFO] (0001:preview): Try to read 32,768 bytes from input source
2019-04-18 10:13:25.186 +0900 [WARN] (0001:preview): Skipped line -:1 (java.lang.NumberFormatException: For input string: ""): "9649379","1","7200","S7A","2019/03/20","TEST","1","TEST0320","01","0","YZK","YZK","3200","","","7200","S7A","MBC1001","","","G","1","2019/04/18 09:48:14","2019/04/18 09:48:14","","","0","","","","","","","","","1","0","9649379","1200002300135","","","2019/04/18 09:48:14","GYK-YZ000","2019/04/18 09:48:14","GYK-YZ000","7043553","2019/04/18 09:48:14.491","0","","","","","","","","","2","","","","","","","","","","","","","","","","","13877897","R01","8","0","","*-1","11","","受入","裾野製作所","ADS豊田(逆輸入)","WH主管工場以外","WH主管工場以外","TAP","裾野製作所","ADS豊田(逆輸入)","ADS豊田(逆輸入) 検前ストア","","逆輸入","訂正","検前受入"

Answer 1

32Kbよりも大きいファイルの場合はデータが途中で切れてしまう場合があるので
preview_sample_buffer_bytesを指定してみてください。
Preview executor

exec:
  preview_sample_buffer_bytes: 65536 # 64KB

カラムの略のところにlongとか使っていますか？""を数値としてパースしているように見えるのですが..

Comments

[raccoondog]

カラム略のところで、longをつかっています。
csvファイルは645バイトになります。

[raccoondog]

結果としてかわらずpreview時に　""つきのレコードは、スキップされてしまいます。

[hiroysato]

じゃあ申し上げたとおり ""を数値として取り込もうとしているのでエラーになっているのだと思います。

Answer 2

config.ymlファイルのin句に

null_string: ""

を追加することにより、csvファイルに"(NULL)"が含まれていても、解析し、bigqueryへバルクインサートされました。