Veganism Social

1 post1 participant0 posts today

Neil CraigTIL the Tranco domain ranking data is available as a public data source in BigQuery - so you can do e.g.:``` select domain, rank from `tranco.daily.daily` where date = date_sub(current_date(), interval 1 day) and domain in ("bbc.co.uk", "bbc.com") order by rank asc ``` <a href="https://tranco-list.eu" rel="nofollow noopener noreferrer" translate="no" target="_blank">https://tranco-list.eu</a><a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> <a href="https://mastodon.social/tags/Tranco" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Tranco</a> <a href="https://mastodon.social/tags/DomainRanking" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#DomainRanking</a>

ClintonDiving into <a href="https://mastodon.social/tags/Vermont" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Vermont</a> wildlife for the <a href="https://mastodon.social/tags/30DayChartChallenge" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#30DayChartChallenge</a> "circle" day! 🦌 Using <a href="https://mastodon.social/tags/Python" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Python</a> & <a href="https://mastodon.social/tags/plotly" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#plotly</a> to compare monthly <a href="https://mastodon.social/tags/Moose" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Moose</a> and <a href="https://mastodon.social/tags/BlackBear" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BlackBear</a> sightings 🐻 Data wrangled with <a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> and <a href="https://mastodon.social/tags/SQL" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#SQL</a>. Any guesses which animal is seen more consistently throughout the year? 😜 <a href="https://mastodon.social/tags/DataViz" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#DataViz</a> <a href="https://mastodon.social/tags/Wildlife" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Wildlife</a> <a href="https://mastodon.social/tags/RadialChart" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#RadialChart</a>

Hacker NewsBigQuery pricing model cost us $10k in 22 seconds<a href="https://www.linkedin.com/posts/yingjun-wu_%F0%9D%90%81%F0%9D%90%A2%F0%9D%90%A0%F0%9D%90%90%F0%9D%90%AE%F0%9D%90%9E%F0%9D%90%AB%F0%9D%90%B2%F0%9D%90%AC-%F0%9D%90%91%F0%9D%90%88%F0%9D%90%83%F0%9D%90%88%F0%9D%90%82%F0%9D%90%94%F0%9D%90%8B%F0%9D%90%8E%F0%9D%90%94-activity-7307736315079405569-F2Ng" rel="nofollow noopener noreferrer" translate="no" target="_blank">https://www.linkedin.com/posts/yingjun-wu_%F0%9D%90%81%F0%9D%90%A2%F0%9D%90%A0%F0%9D%90%90%F0%9D%90%AE%F0%9D%90%9E%F0%9D%90%AB%F0%9D%90%B2%F0%9D%90%AC-%F0%9D%90%91%F0%9D%90%88%F0%9D%90%83%F0%9D%90%88%F0%9D%90%82%F0%9D%90%94%F0%9D%90%8B%F0%9D%90%8E%F0%9D%90%94-activity-7307736315079405569-F2Ng</a><a href="https://mastodon.social/tags/HackerNews" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#HackerNews</a> <a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> <a href="https://mastodon.social/tags/Cost" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Cost</a> #$10k <a href="https://mastodon.social/tags/DataAnalysis" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#DataAnalysis</a> <a href="https://mastodon.social/tags/CloudComputing" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#CloudComputing</a> <a href="https://mastodon.social/tags/PricingModel" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#PricingModel</a> <a href="https://mastodon.social/tags/TechNews" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#TechNews</a>

N-gated Hacker News🍕🎨 "Map of Python" is the digital dabbling of a cartographer, lost in the vast jungle of 500,000+ <a href="https://mastodon.social/tags/Python" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Python</a> packages, desperately trying to create art from JSON blobs. 🧩🤷‍♂️ But hey, at least there's <a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> to save the day from the horror of actually downloading data! 🚀🧐 <a href="https://fi-le.net/pypi/" rel="nofollow noopener noreferrer" translate="no" target="_blank">https://fi-le.net/pypi/</a> <a href="https://mastodon.social/tags/DataVisualization" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#DataVisualization</a> <a href="https://mastodon.social/tags/Cartography" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Cartography</a> <a href="https://mastodon.social/tags/ArtInTech" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#ArtInTech</a> <a href="https://mastodon.social/tags/HackerNews" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#HackerNews</a> <a href="https://mastodon.social/tags/ngated" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#ngated</a>

Neil CraigFFS. 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, invalidSo it's impossible to have column descriptions for MVs? That sucks.<a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a>

Neil CraigWhilst 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.<a href="https://mastodon.social/tags/BBC" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BBC</a> <a href="https://mastodon.social/tags/NodeJS" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#NodeJS</a> <a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> <a href="https://mastodon.social/tags/GoogleCloud" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#GoogleCloud</a>

Neil CraigAfter several iterations, I think I've finally got my log ingest pipeline working properly, at scale, using the <a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> 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! 🤞

Neil Craig...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.<a href="https://cloud.google.com/nodejs/docs/reference/bigquery-storage/latest/bigquery-storage/managedwriter.writerclient" rel="nofollow noopener noreferrer" translate="no" target="_blank">https://cloud.google.com/nodejs/docs/reference/bigquery-storage/latest/bigquery-storage/managedwriter.writerclient</a><a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> <a href="https://mastodon.social/tags/GoogleCloud" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#GoogleCloud</a> <a href="https://mastodon.social/tags/NodeJS" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#NodeJS</a> <a href="https://mastodon.social/tags/WebDev" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#WebDev</a>

Neil CraigAfter 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.<a href="https://cloud.google.com/nodejs/docs/reference/bigquery-storage/latest/bigquery-storage/v1.bigquerywriteclient#_google_cloud_bigquery_storage_v1_BigQueryWriteClient_appendRows_member_1_" rel="nofollow noopener noreferrer" translate="no" target="_blank">https://cloud.google.com/nodejs/docs/reference/bigquery-storage/latest/bigquery-storage/v1.bigquerywriteclient#_google_cloud_bigquery_storage_v1_BigQueryWriteClient_appendRows_member_1_</a><a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> <a href="https://mastodon.social/tags/NodeJS" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#NodeJS</a> <a href="https://mastodon.social/tags/WebDev" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#WebDev</a>

Neil CraigI 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 (⬇️) <a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> <a href="https://mastodon.social/tags/GoogleCloud" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#GoogleCloud</a> <a href="https://mastodon.social/tags/BBC" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BBC</a>

Eph Zero :emacs: 💪🏻 🚲 🤘🏻Welp, I was laid off today. Anyone need an SQL monkey? [UPDATED to include location and link to résumé] Physical location: <a href="https://masto.snoozetown.org/tags/madison" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#madison</a> <a href="https://masto.snoozetown.org/tags/wisconsin" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#wisconsin</a> <a href="https://masto.snoozetown.org/tags/usa" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#usa</a> Link to résumé: <a href="https://drive.google.com/file/d/1RkZ_buZxopuJOAgOh0Mp0ck0onF0Scex/view?usp=sharing" rel="nofollow noopener noreferrer" translate="no" target="_blank">https://drive.google.com/file/d/1RkZ_buZxopuJOAgOh0Mp0ck0onF0Scex/view?usp=sharing</a><a href="https://masto.snoozetown.org/tags/SQL" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#SQL</a> <a href="https://masto.snoozetown.org/tags/bigquery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#bigquery</a> <a href="https://masto.snoozetown.org/tags/lookerstudio" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#lookerstudio</a> <a href="https://masto.snoozetown.org/tags/gcp" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#gcp</a> <a href="https://masto.snoozetown.org/tags/downtowork" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#downtowork</a> <a href="https://masto.snoozetown.org/tags/laidoff" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#laidoff</a> <a href="https://masto.snoozetown.org/tags/opentowork" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#opentowork</a> <a href="https://masto.snoozetown.org/tags/remote" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#remote</a> <a href="https://masto.snoozetown.org/tags/getfedihired" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#getfedihired</a>

Neil CraigThe 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.<a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> <a href="https://mastodon.social/tags/GoogleCloud" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#GoogleCloud</a> <a href="https://mastodon.social/tags/NodeJS" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#NodeJS</a> <a href="https://mastodon.social/tags/Node" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Node</a>

Neil CraigLong 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.<a href="https://mastodon.social/tags/GoogleCloud" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#GoogleCloud</a> <a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a>

NblogLinksDon’t Lose Your Google Universal Analytics Data Ever Again!<a href="https://social.outsourcedmath.com/display/9b1e8f93-1066-7fe7-5b11-0e5739686650" rel="nofollow noopener noreferrer" translate="no" target="_blank">https://social.outsourcedmath.com/display/9b1e8f93-1066-7fe7-5b11-0e5739686650</a>

Neil CraigI'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! <a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> <a href="https://mastodon.social/tags/NodeJS" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#NodeJS</a> <a href="https://mastodon.social/tags/Data" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Data</a> <a href="https://mastodon.social/tags/BBC" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BBC</a>

Neil CraigDuring 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 <a href="https://mastodon.social/tags/WebDev" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#WebDev</a> <a href="https://mastodon.social/tags/Data" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Data</a> <a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> <a href="https://mastodon.social/tags/BBC" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BBC</a> <a href="https://mastodon.social/tags/Euros" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Euros</a>

Neil CraigI wish BigQuery had better IP address functions e.g. a function which detects whether a given IP address is in a CIDR block. <a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a>

Neil CraigWeird...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. <a href="https://mastodon.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> <a href="https://mastodon.social/tags/Node" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Node</a>

Flüpke"""Open Source""", CC BY SA 4.0Data is only <a href="https://chaos.social/tags/Google" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#Google</a> <a href="https://chaos.social/tags/BigQuery" class="mention hashtag" rel="nofollow noopener noreferrer" target="_blank">#BigQuery</a> 🤡<a href="https://rumarchive.com/datasets/" rel="nofollow noopener noreferrer" translate="no" target="_blank">https://rumarchive.com/datasets/</a>

Recent searches

Search options

Administered by:

Server stats:

#bigquery