8 ms·
Machine Learning in Google Bigquery
- djhworld 8y agoWe're trialling BigQuery at the moment, pushed about 130TB into it last week (about ~300 billion rows) and have been blown away by the performance of it. It's a bit of a shame they never released a paper on how it works, well, nothing since Dremel anyway. And Collosus isn't public either. I can already see this in built ML stuff being useful for trialling models, especially as it's built right into the SQL.
- puzzle 8y agoThere is information about Colossus: the original GFS paper, an interview with Sean Quinlan with McKusick on Colossus improvements, as well as a talk by Denis Serenyi on more of the gory details, e.g. rebalancing. There was also a public talk on D, the GFS chunkserver replacement, but there don't seem to be recordings or slides from that.
- kyrra 8y agoThere have been some blog posts about it, but you are right, no whitepapers. https://cloud.google.com/blog/big-data/2016/04/inside-capacitor-bigquerys-next-generation-columnar-storage-format https://cloud.google.com/blog/big-data/2016/04/inside-capaci... https://cloud.google.com/blog/big-data/2016/01/bigquery-under-the-hood https://cloud.google.com/blog/big-data/2016/01/bigquery-unde...
- minimaxir 8y agoAt the Reddit talk at Google Next, the VP of Engineering noted that they moved to BigQuery and saved in both performance and cost. We at BuzzFeed have seen similar results for migrating to BigQuery (and we did a talk about it at Next too, although it’s not online yet)
- minimaxir 8y agoReddit talk: https://youtu.be/tKISLQ87GO8 https://youtu.be/tKISLQ87GO8 BuzzFeed talk: https://youtu.be/74PKZmL-Qjs https://youtu.be/74PKZmL-Qjs
- fhoffa 8y agoI really enjoyed this slide from the BuzzFeed talk "Hundreds of terabytes across 300+ tables, 100K+ queries a day" on BigQuery. I quoted the reddit talk on the thread above/below :).
- gipp 8y agoHaving recently moved to data science at another big gcp early adopter, BQ was a complete game changer having come from redshift and hive
- antonvs 8y agoHow do you find the querying costs? It seems like for many use cases, you'd have to pay quite a bit of attention to partitioning the database in a way that minimizes query costs.
- minimaxir 8y agoThe relatively new date-partitioning feature for tables has cut down query costs/time significantly.
- fhoffa 8y agoAnd with the just announced clustering now you get even better costs and performance. Disclosure: I'm https://twitter.com/felipehoffa https://twitter.com/felipehoffa and I work for Google Cloud. And I'm really excited to reprocess all public tables into clustered ones.
- gipp 8y agoCost (or rather bytes scanned) is sent back from the API on any queries or dry run/validation calls. There's a common chrome extension to put it in the legacy ui, and I believe the beta ui has it baked in (as part of validation)
- djhworld 8y agoFor this trial we are transferring some data from AWS, which is already partitioned in S3 by quite a few dimensions, including DAY. As BQ only supports DAY partitioning, I had to create multiple tables that represent the other dimensions, but it seems to work OK, if a little clunky when you have to use wildcards/_TABLE_SUFFIX
- vgt 8y agoBq pm here. No papers, yes, but lots of talks and blog posts. Here's a list: https://medium.com/@thetinot/bigquery-required-reading-list-71945444477b https://medium.com/@thetinot/bigquery-required-reading-list-... Happy to answer any questions!
- polskibus 8y agoHow would you compare it to a traditional OLAP solutions (MS SQL Analysis Services in OLAP model (not tabular), Essbase etc.) in terms of : - multidimensional / hierarchy modeling for analytical purposes - permissioning model (roles with element-level granularity, ie. User A - allow Country=USA, User B - allow Product=Bike) - historical modeling (built-in slowly-changing dimensions support) What is the underlying storage model? Is it a column store ? Is it closer to traditional row-based stores? Are you taking advantage of GPUs or other dedicated hardware to accelerate BQ?
- kornish 8y agoIf you haven't read it already, I strongly suggest reading the original Dremel paper [0]. It's no doubt somewhat out of date, but I believe BQ is based in Dremel. tl;dr for underlying storage model: distributed column store which pushes computation down a tree to leaf nodes to parallelize disk I/O. Parent nodes aggregate computations before returning to the client. [0]: https://ai.google/research/pubs/pub36632 https://ai.google/research/pubs/pub36632
- vgt 8y agoThis video gives a walkthrough of the new version of Dremel that's been running in production for past 3 years (no paper sorry!): https://www.youtube.com/watch?v=UueWySREWvk https://www.youtube.com/watch?v=UueWySREWvk
- nrjames 8y agoDid you all work together with the Firebase team on the recent schema change? We're scrambling to mitigate the ~2x cost increase that caused for us, since it blew our analytics budget out of the water.
- ceocoder 8y agoWe (Sojern) have been using BigQuery since late 2015, we moved from Hive. It has been an amazing experience in terms of cost savings (managed infra, operational cost of a hadoop cluster), and performance. We use it across the org from analytics, ingestion, reporting, ML, and many more uses.
- mooneater 8y agoOnly logistic and linear regression for now. For use cases it covers, this will save a lot of plumbing.
- Karrot_Kream 8y agoIndependent of DL, I'm curious why these two regression cases haven't been made available in a SQL-like interface until now. Kudos to Google for putting this in the hand of folks who otherwise just use SQL.
- mark_l_watson 8y agoGood point! I wouldn’t be surprised to see a Postgres plugin for this. Wouldn’t get the vast scalability but it would offer convenience.
- ximeng 8y agohttps://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE https://www.postgresql.org/docs/current/static/functions-agg...
- YurtleTheTurtle 8y agoOracle has had linear regression for well over a decade now.
- cottonseed 8y agoLinear regression makes a lot of sense since it can be done streaming in a single pass. We have it as an aggregator in Hail: https://hail.is/docs/devel/aggregators.html#hail.expr.aggregators.linreg https://hail.is/docs/devel/aggregators.html#hail.expr.aggreg... I wonder how they're doing logistic regression. It doesn't have a closed form and normally solved with an iterative, multi-pass optimization. edit: Read more closely. They're using (non-stochastic) batch gradient descent.
- salimmadjd 8y agoThis is very exciting for us, even at its nascent limited ability point. Compared to many players in different verticals, our data is small. But in our vertical of asthma care, we probably have one of the largest (possibly the largest) asthma data. We've been looking at different way of plumbing the data to automate and run some rudimentary analysis on it, since we found BigQuery a bit limiting. Now seeing this announcement, it could be a great start for us. I hope this is just a start and people like us can send Google Cloud team a wish list as we come across various needs. Good job and thanks to everyone behind this release.
- cottonseed 8y agoCan I ask more about the structure of the data and what you'd like to do with it? My email is in my profile.
- akadien 8y agoI appreciate how convenient it is to have statistical analysis tools available directly in BQ SQL, but are linear and logistic regression really considered "machine learning"?
- azurezyq 8y agoActually most of online ads ctr prediction machine learning systems are still logistic regression based until very recently. It's simple but very effective.
- Eridrus 8y agoYes, it definitely is.
- Xorlev 8y agoJust because it's not a neural net doesn't make it not ML. Also, can echo the other folks here: linear models and basic logistic regression are still competitive.
- akadien 8y agoI wholeheartedly agree with you. I'm suggesting that it might be time to have the counter conversation that just because it has numerical statistical analysis does not make it ML. I've been around the field for a couple of decades and understand linear regression is covered in the first pages of chapter one. If that is the standard, then Excel can claim to have had machine learning capabilities for years.
- hotpotjunkie 8y agoGood, I hope this trend of shoving ML into SQL (instead of the other way around) continues. I always thought it was silly that every "data wrangling" system like Pandas and R needed to (poorly) re-invent SQL.
- halflings 8y agoUnless you present clear arguments, I'd refrain from saying that Pandas is "poorly re-inventing SQL". Pandas is now the standard for data analysis (as long as things fit into memory). It's much much easier to debug than a SQL command. You can write operations as a succession of small logical steps (instead of one huge query that is hard to debug). It's raw Python, so you can do something like: df.groupby('movie_id').agg(dict(ratings='median', price=lambda p : np.percentile(p, .95))).plot.bar(bins=50)
- alexgmcm 8y agoYeah, also in Pandas you can do stuff that otherwise requires writing a custom reducer or UDAF in which case you aren't using SQL anyway. I just use SQL to grab and if necessary aggregate the data and then do everything else in Pandas - using Python custom reducers to deployed trained models although we are migrating to GCP now so soon that won't be necessary.
- dionna767675 8y agoLearn how to run a business from the ground up plus make money online get your business certificate low affordable payment plan real legitimate company Internet marketing opportunity the proven online destination for small business consulting and tools for small business owners and entrepreneurs. Founded in 1996 by Fran Tarkenton, we are driven by one goal: to help you start, run, and grow your business. After an 18 year career in the NFL that took him to the Hall of Fame, Fran dedicated his life to business, and over his 50 years as an entrepreneur he has built more than 20 successful companies get the business training you need to help your business grow http://gosmallbiz.club http://gosmallbiz.club
- spocklivelong 8y agoDoes anyone here tried Druid? I hear at Druid performance is much better in terms of response time, especially for arbitratry queries over large set of dimensions. Did anyone do an in-depth comparison between Bq vs Druid?