Loading CSV with NULLs columns using bq load

Question

I am trying to upload a CSV file(TSV actually) generated in mysql(using outfile) into Bigquery using bq tool. This table has following schema:

enter image description here

Here is the sample data file:

"6.02"  "0000"  "101"   \N  "Md Fiesta Chicken|1|6.69|M|300212|100|100^M Sourdough|1|0|M|51301|112|112" "6.5"   \N  "V03"   "24270310376"   "10/17/2014 3:34 PM"    "6.02"  "30103" "452"   "302998"    "2014-12-08 10:57:15"   \N

And this is how I try to upload it using bq CLI tool:

$ bq load -F '\t' --quote '"' --allow_jagged_rows receipt_archive.receipts /tmp/rec.csv
BigQuery error in load operation: Error processing job
'circular-gist-812:bqjob_r8d0bbc3192b065_0000014ab097c63c_1': Too many errors encountered. Limit is: 0.
Failure details:
- File: 0 / Line:1 / Field:16: Could not parse '\N' as a timestamp.
Required format is YYYY-MM-DD HH:MM[:SS[.SSSSSS]]

I think the issue is that updated_at column is NULL & hence skipped. so any idea how can I tell it to consider null/empty columns?

Very similar to stackoverflow.com/q/13050296/560648 (probably enough to call it a dupe); this looks like a format discrepancy between the export and input tools (\N is not "standard" CSV) — Lightness Races in Orbit
@Strawberry What you mean? "it's amazingly unlikely that payable would be a FLOAT" why can't be float? Its the amount payable. if not float, what datatype would it be? — CuriousMind
decimal. People can be pretty sticky about those piddly little fractions! — Strawberry

Strawberry Strawberry · Accepted Answer · 2015-01-03T18:14:48

CuriousMind - This isn't an answer. Just an example of the problem of using floats instead of decimals...

CREATE TABLE fd (f FLOAT(5,2),d DECIMAL(5,2));

INSERT INTO fd VALUES (100.30,100.30),(100.70,100.70;

SELECT * FROM fd;
+--------+--------+
| f      | d      |
+--------+--------+
| 100.30 | 100.30 |
| 100.70 | 100.70 |
+--------+--------+

SELECT f/3+f/3+f/3,d/3+d/3+d/3 FROM fd;
+-------------+-------------+
| f/3+f/3+f/3 | d/3+d/3+d/3 |
+-------------+-------------+
|  100.300003 |  100.300000 |
|  100.699997 |  100.700000 |
+-------------+-------------+

SELECT (f/3)*3,(d/3)*3 FROM fd;
+------------+------------+
| (f/3)*3    | (d/3)*3    |
+------------+------------+
| 100.300003 | 100.300000 |
| 100.699997 | 100.700000 |
+------------+------------+

But why is this a problem, I hear you ask?

Well, consider the following...

SELECT * FROM fd WHERE f <= 100.699997;
+--------+--------+
| f      | d      |
+--------+--------+
| 100.30 | 100.30 |
| 100.70 | 100.70 |
+--------+--------+

...now surely that's not what would be expected when dealing with money?

Loading CSV with NULLs columns using bq load

2 Answers