Diving into #Vermont wildlife for the #30DayChartChallenge "circle" day! 
Using #Python & #plotly to compare monthly #Moose and #BlackBear sightings 
Data wrangled with #BigQuery and #SQL. Any guesses which animal is seen more consistently throughout the year? 
#DataViz #Wildlife #RadialChart
FFS. Turns out (after I built a feature) that you can't supply a schema for BigQuery Materialised Views.
> Error: googleapi: Error 400: Schema field shouldn't be used as input with a materialized view, invalid
So it's impossible to have column descriptions for MVs? That sucks.
Whilst migrating our log pipeline to use the BigQuery Storage API & thus end-to-end streaming of data from Storage (GCS) via Eventarc & Cloud Run (read, transform, enrich - NodeJS) to BigQuery, I tested some big files, many times the largest we've ever seen in the wild.
It runs at just over 3 log lines/rows per millisecond end-to-end (i.e. inc. writing to BigQuery) over 3.2M log lines.
Would be interested to know how that compares with similar systems.
Google did finally post an outage status report 
After several iterations, I think I've finally got my log ingest pipeline working properly, at scale, using the #BigQuery Storage API.
Some complications with migrating from the "legacy" "streaming" (it's not in the sense of code) API have been really hard to deal with e.g.:
* A single row in a write fail means the entire write fails
* SQL column defaults don't apply unless you specifically configure them to
* 10MB/write limit
I rewrote the whole thing today & finally things are looking good! 
...and after even more debugging, it turns out that the reason for BigQuery `appendRows()` write failures was that large write data volumes (~5k rows or ~5MB of data) exceed the (undocumented) default `createStreamConnection()` timeout 
.
Even the units of the config option (once you find it) are not documented . It's in milliseconds as it turns out.
I upped the timeout to 120s and the failures go away. FFS.
After a goodly amount of debugging, it turns out that the BigQuery Storage API (for NodeJS at least) function `appendRows()` (which is how you tell it to write data to BigQuery) fails every time if you give it "too much" data.
This is not documented (and the docs are somewhat minimal for such an important function). I have an open case with Google & have asked for the docs to be improved.
Thought it might help others to note this.
I deployed (then had to revert) an update to the log processing pipeline which ingests our CDN access logs in Google Cloud Run then writes to BigQuery.
The change migrated from the "legacy" BigQuery API to the Storage API. Thought it was worth sharing some write performance improvements seen in the Storage API:
* P99 0.5s (30%) lower
* p95 155ms (25%) lower
* p75 58ms (20%) lower
* p50 43ms (43%) *higher*
That's on 1/2 size Run containers so higher p50 is worth it (
)
#BigQuery #GoogleCloud #BBC
Welp, I was laid off today. Anyone need an SQL monkey?
[UPDATED to include location and link to résumé]
Physical location: #madison #wisconsin #usa
Link to résumé: https://drive.google.com/file/d/1RkZ_buZxopuJOAgOh0Mp0ck0onF0Scex/view?usp=sharing
The BigQuery Storage API has (AFAIK) undocumented behaviour as-goes write failures which differs *dramatically* from the "legacy API":
If you tell the BQ Storage API to `appendRows()` and *any* rows fail to be written (due to e.g. data type/range incompatibility), *all* rows will in fact not to be written to BQ - even though the (Node) lib will only tell you the rows which are incompatible.
It took me some time to find this out. Hoping it helps someone.
Long shot...
Is anyone using the Node JS SDK for the BigQuery Storage API with `appendRows()`?
It seems to fail *all* writes if >= 1 row passed to `appendRows()`fails which makes it unusable for me.
The "legacy" API has a `skipInvalidRows`option but I can't find one with the Storage API. I've raised a ticket but hoping someone'll know.
I've been migrating our log ingest pipeline from writing (JSON) to BigQuery via `table.insert()` to using the BigQuery Storage API (which converts to a protobuf representation of the JSON you feed it).
The example code in docs is god-awful but I have persevered & made it work.
Storage API:
- Is >2x faster thus far
- Is ~10x cheaper (IIRC)
- Needs timestamps with usec res, `table.insert()` is in seconds 
Pleased with it despite the pain. I'll polish it next week!
#BigQuery #NodeJS #Data #BBC
During last night's England vs Denmark match, we hit just over 750k log lines/second (mean minutely, the real peak will've been higher) on my log pipeline. The minutely peak was 45.1M log lines/minute.
Bearing in mind this only processes our web edge logs & a small subset of our media logs (plus supporting services) & the match wasn't super busy, that's quite a lot. I'll see if I can find out what the overall peak was. I'm guessing way into the millions
#WebDev #Data #BigQuery #BBC #Euros
I wish BigQuery had better IP address functions e.g. a function which detects whether a given IP address is in a CIDR block.
#BigQuery
Weird...I have been working to migrate our log processing pipeline to use streams all the way from the log file itself to BigQuery...got it all working and was very pleased until I deployed it to the dev env and writes all failed due to lack of `bigquery.tables.create` permisison on the svc account (despite the destination tables existing).
Added that and it works.
Seems `table.createWriteStream()` (node) uses an implicit `create if not exists` or similar.
#BigQuery #Node
Had a BigQuery scheduled query failure overnight:
```
Bad int64 value
```
Looks like an overflow in a `CAST(<value> as INT64)`.
Anyway, TIL `SAFE_CAST()` (returns `null` on error).
#BigQuery #SQL #WebDevelopment
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions#safe_casting
detailed and genuinely useful blog on how we built our internal data warehouse on #ClickHouse at ClickHouse
https://clickhouse.com/blog/building-a-data-warehouse-with-clickhouse
#ClickHouse #DataWarehouse
starring
#Superset #AppFlow #Airflow #Redis #Docker #opensource #RDS #PostgreSQL #DBT
and including data from
#Salesforce #AWS #S3 #BigQuery #M3ter #Segment #Marketo
