20 ms·
Google makes BigQuery available in Sheets
- spaceheretostay 8y agoAll this fancy stuff, but where's my Sets at? Google took down the website like 15 years ago. I used that thing daily. Then they put it into Google Sheets. You could type "apple" then "pear" then "orange" and then select cells, drag, and you'd get a list of fruits! Some you never heard of before! I looked around and Google has a patent on it, and seemingly no public implementation. This makes me super sad because I honestly considered Sets one of the most useful exploration and ideation tools ever created. BigQuery in Sheets, cool I guess? I want Sets back.
- steve19 8y agoTry this: http://wordgrabbag.com/ http://wordgrabbag.com/ Here is Google's Patent: http://patft.uspto.gov/netacgi/nph-Parser?OS=pn/7,350,187&RS=PN/7,350,187&S1=7,350,187.PN.&Sect1=PTO2&Sect2=HITOFF&d=PTXT&f=G&l=50&p=1&r=1&u=/netahtml/PTO/search-adv.htm http://patft.uspto.gov/netacgi/nph-Parser?OS=pn/7,350,187&RS...
- appleflaxen 8y agothat's the same functionality, but the results are not as good. I put in: fat obese porcine I get: wild-type hyperactive amnesic misshapen narcissistic slimy cancerous motile putrid gravid
- Amazonerh 8y agoWhat's a use case for this?
- mej10 8y agoI wonder how enforceable that patent is.
- deleted 8y ago[deleted]
- londons_explore 8y agoI don't know how Google's implementation of sets worked, but a simple implementation you could do yourself in a Jupiter notebook is to load up some pretrained word vectors, normalize all vectors, look up the vectors for all words in your seed set, take the mean of them, and then look for the nearest neighbours. Stick it on a website and then the rest of the world can use it too!
- davnn 8y agoHave a look at https://projector.tensorflow.org/ https://projector.tensorflow.org/
- hnuser123456 8y agoStill not as cool as Google Squared, where 10 years ago, you could create rows of any nouns and cols of any adjectives, and it would automagically research and fill out all the data for you according to its best guess. Create rows of car models, cols of price, seating, horsepower, safety rating, stuff like that.
- dannyw 8y agoThat’s all in Google Knowledge Graph. They dropped it because it makes finding information too easy. And hence, competitor search engines could get info out of it super cheaply.
- polskibus 8y agoHow's bigquery performance in comparison to clickhouse? When is it worth switching from one to the other?
- wikibob 8y agoBQ is considerably more performant.
- msquog 8y agoThis is uninformed and unsubstantiated. The answer depends massively on the intended use case.
- polskibus 8y agoWhat are the use cases where one would shine and the other wouldn't? I'm interested in real life usage of any of those on >100 GB datasets.
- monsieurbanana 8y agoWith bigquery the cost is per query. If you do a "SELECT * FROM AAA" you'll pay whatever the size of the table AAA is, price being 5$ per TB. If you do a "SELECT field1, field2 FROM AAA" you'll pay only for the total size of field1 and field2 rows. So you usually want to use BigQuery in situations where you don't need to query data all the time, but rather a fixed number of times a day. Now about performance: BigQuery queues queries, so you don't have a guaranteed time. It can take a couple of seconds before your query starts running, it can take longer. If you need something that responds in < xxx ms, BigQuery is not it. But the queries themselves are fast. If you need to query across petabytes of data, as a simple BigQuery query will gladly run on however many dozens or hundreds of instances it needs, at no additional cost for you (since you only pay by the size of your data queried). It's really a great example of serverless. You can run your query across 100 instances, but you only use those instances for a few seconds.
- crystaln 8y agoCool. I wonder when they will shut it down. I largely ignore Google products announcements these days. Why bother getting excited about things that won't last?
- mda 8y agoThis is getting tired and adds nothing to the discussion.
- coldtea 8y agoIf one is not familiar with Google's background on the matter, it's the number one thing they should know.
- jasonvorhe 8y agoNo, the first thing you should know is that Google consumer products are not Enterprise features.
- puszczyk 8y agoIs Sheets consumer or enterprise?
- joshuamorton 8y agoSheets integration with bigquery that has an enterprise pricing plan is enterprise. There's also a decent argument to be made that sheets is an enterprise product too. It certainly is for enterprise customers. Disclosure: I work in Google Apps, but not on sheets or docs.
- deleted 8y ago[deleted]
- mda 8y agoNo it is not the number one thing. This comment is especially irrelevant in Google Cloud context.
- polskibus 8y agoHow does pricing work here? Say I have a pivot table on underlying 10TB data. I share the sheet with 10 people, each one refreshes it 100 times a day. Do I pay for each data refresh, ie 10TB * 10 * 100= 10000TB/day, that would be $50000/day if I read the pricing table right. Can someone more familiar with bigquery verify that?
- gundmc 8y agoWe'll have to wait for details about connected sheets, but BQ itself caches queries by default for 24 hours and you're not charged for cache hits. I assume something similar applies.
- polskibus 8y agoThat would assume the underlying data does not change. If you use BiqQuery with ingest runnig 24/7, you often don't want to be served from cache. Besides, filters and slicers can be modified on a per user basis, that would trigger recalculations. Again - not sure if a cache would handle this.
- lukasb 8y agoBigQuery is usually intended for data warehouse use cases, where missing the most recent 23 hours of data is less of a big deal. It's intended to feed things like exec dashboards, not production systems.
- hulahoof 8y agoI believe the views will refresh when the underlying data changes, so if you are streaming or inserting frequently the costs could add up
- SmirkingRevenge 8y agoBig query isn't a great use case if data that needs to be queried repeatedly and is updating that frequently, I don't think. With streaming inserts, you probably should generally only be doing inserts on the latest date/time based partition, so that you can take advantage of cached queries for all data but the most recent date/time partition.
- danielecook 8y agoI’ve had trouble getting more than 10k rows in google sheets. It will be interesting to see how well this works.
- ramraj07 8y agoI have a couple sheets that are very close to the 2 million cell limits, in which almost every cell either does a vlookup or is being queried or is displaying some Google sheet SQL result. Sheet takes time to load but still works fine!
- hyperbovine 8y agoHave to ask, what is the use for such a Frankenstein?
- richjdsmith 8y agoBoy, I'm not sure quite why, but the idea of a spreadsheet with nearly 2 million cells stresses me out... Why that over a DB?
- seektable 8y agowell, for >10k rows most likely it is better to use more specialized tools to create pivot tables. Google Sheets as browser app just not intended to do this.
- paulcole 8y ago> The beta of connected sheets should go live within the next few months. In case anyone’s looking for it, there’s the buried lede. Whenever Google announces something like this, don’t hold your breath. Remember that robo-calling assistant they demoed awhile back? I remember that should’ve gone live within a few months, too.
- drivebycomment 8y agohttps://9to5google.com/2019/04/08/google-duplex-expansion-pixel-assistant/ https://9to5google.com/2019/04/08/google-duplex-expansion-pi... says it's been live and expanding.
- jasonvorhe 8y agoWhy do you spread such negativity if you haven't even fact checked that what you're saying is true?
- lopuhin 8y agoThat's the quote from the article. Do you have any additional info on when that would become available? The link from another reply is completely unrelated, as of writing this.
- tacomonstrous 8y agoIt's already working on Pixel devices in the US. Source: I used it.
- lopuhin 8y agoSorry, just to clarify, is that BigQuery integration into Sheets your are talking about? Can't find it in the web UI.
- tacomonstrous 8y agoNo I was referring to the robocalling feature. It's an actual product now.
- deleted 8y ago[deleted]
- vanhuyphuyen 8y agohaha, so loce
- fouc 8y agoI thought this was a reference to the "lady in the streets, freak in the sheets" quote for a second.
- brodouevencode 8y agoNerd on the streets, BigQuery in the sheets?
- cosmie 8y agoSimilar functionality called PowerPivot/PowerQuery[1][2] exists in Excel. It's hands down one of my favorite things to use Excel for, and also one of the least understood/known features by most Excel users. I can't wait to see how Google's flavor of it for Sheets works. [1] It's officially called "Get & Transform" now, but when it came out one Excel 2010 it was called PowerQuery, and still gets referenced that way everywhere (including the official help docs). [2] https://support.office.com/en-us/article/get-transform-in-excel-881c63c6-37c5-4ca2-b616-59e18d75b4de https://support.office.com/en-us/article/get-transform-in-ex...
- mritchie712 8y agoThis was one of the things I missed when moving from Excel to Sheets and led to me building https://seekwell.io https://seekwell.io. It lets you grab data from your database and drop it in Sheets using just SQL (Postgres, MySQL, SQL Server and Snowflake). We've also built a full SQL and Python IDE with a shared code repository, but solving the simple problem of "write SQL -> get data in a Sheet" was where we started.
- wodenokoto 8y agoGoogles own blog [1] announcing the feature for G Suite Business, from back in January. This is a great feature that I have been searching for, for a long time. [1] https://cloud.google.com/blog/products/g-suite/connecting-bigquery-and-google-sheets-to-help-with-hefty-data-analysis https://cloud.google.com/blog/products/g-suite/connecting-bi...
- lmkg 8y agoThat looks different. The Data Connector, which you linked, lets you write a query to BQ and have the results in Sheets. Connected Sheets sounds like it's actually a front-end to the underlying data. Notably, the Data Connector has a row limit of 10,000 results, while the Connected Sheets claim to surpass the regular row limits of Google Sheets in general.
- dalbasal 8y agoFor all the complaints about spreadsheets, attempts to replace them and such... They are a very reslilient paradigm. The way the paradigm and learning curve work just seems to work for a lot of people. I reckon it's still the most popular plaform for coding, data engineering, analytics & data science... using liberal definitions for all those terms. It also seems like something that's in google's wheelhouse.
- StevePerkins 8y agoEarlier this week, someone was passing this tweet around the office, which I believe captures the cold truth succinctly: > "All enterprise software competes with Excel. > All productivity software competes with emailing things to yourself." https://twitter.com/pavelasamsonov/status/1106534188159778817 https://twitter.com/pavelasamsonov/status/110653418815977881...
- zikzak 8y agoThis is very, very true in my experience. After years and years of trying out all, I keep coming back to a list of tasks in a sheet so I've combined the two. It's just so easy to reconfigure or query. And with Google Sheets on my phone, it is portable. Add an IFTTT note widget to add a row to my sheet and I've got instant capture. Finally, you can do whatever you want with it using Google apps script (e.g. send reminders). It's hard to beat. Despite all the capabilities, I still keep it simple and focus on capture and review. I could switch this to a list in a text file or on paper very easily and maintain the core of my functionality. I always think "people built cathedrals and pyramids without all this fancy software, it isn't the key to getting things done".
- Consultant32452 8y agoAt my current job we were supposed to have switched to Rally. However, the PMs prefer Excel. The result is that a spreadsheet on the PM's computer is the real system of record and sometimes things make it into Rally. This is a terrible experience. I've abandoned both systems and just work on whatever I feel like. Luckily I have enough clout and a slightly unique role that I can get away with doing this. I feel awful for the devs who are supposed to be working on the normal dev tasks.
- savrajsingh 8y agoLast time I checked, it was still hard to get a Google CloudSQL DB into BigQuery, so I’m surprised they did the sheets integration first. We used a third party that just copied data from our CloudSQL to BigQuery.
- skiman10 8y agoWhat tool did you use?
- faizshah 8y agoYea this is surprisingly annoying. I did CloudSQL -> csv -> dataprep -> bigquery. For about 10 tables with ~20 columns each. A job that should be less than an hour took over 6 hours due to various annoying datatype issues. Another interesting thing is that BigQuery doesn't have Full Text Search so you need to go back into postgres or use elastisearch to use FTS which is beyond annoying.
- vgt 8y agoCheck out the newly announced data fusion
- falcor84 8y agoI've tried using the current version of Sheets App Script for some advanced business functionality, but it's horrendously slow - taking over 10 seconds to loop over just 5000 rows. And they're using an ancient version of js, making development annoying. I really hope that this announcement would herald a renewed interest on their side in improving Sheets as a whole.
- pcmaffey 8y agoYes, I found it much faster to do everything in memory, minimizing the number of reads/writes. And it was a bit of shocker writing es5 js again.
- mises 8y agoI wish spreadsheets didn't get as much unnecessary hate as they do. They are a pretty great tool, and can do almost anything. They are to finance/accounting guys as shells are to many of us.
- SquishyPanda23 8y agoFinance guys and gals have shells like R or Python. Spreadsheets are GUIs for people who prefer that to shells.
- mises 8y agoDon't kid yourself, most finance guys still use Excel. Maybe a few younger ones in the valley use tools like R, Python, etc. but not in most places.
- SquishyPanda23 8y ago> Don't kid yourself, most finance guys still use Excel. I didn't say anything about that. You said spreadsheets are like shells for finance people. I was just pointing out that shells for finance people exist and that most prefer not to use them.
- hn_throwaway_99 8y agoI don't think they get unnecessary hate, it's just that they have a tendency to grow in complexity to the point where a spreadsheet is no longer the right job but is still in use. I don't think it happens as much any more but the same analogy could be used for shell scripts. You may have a process that a couple shell scripts are useful for, but they can also metastasize to the point where only the original developer (if even) can maintain them.
- goobynight 8y agoExactly. To me, if the sheet is larger than what I can keep on a decent sized screen, it's probably time to move it. Not definitely, but probably.
- minimaxir 8y agoIt's worth noting that there has been a Sheets connector for BigQuery since January, although only for G Suite customers: https://cloud.google.com/blog/products/g-suite/connecting-bigquery-and-google-sheets-to-help-with-hefty-data-analysis https://cloud.google.com/blog/products/g-suite/connecting-bi... This announcement seems like an expansion on what's above (e.g. pivot tables). I've tested what's above for my org and there's a lot of promise. (only criticism is creating formulas in the query don't work automatically since they are returned as Text cells; workaround is to cast as Automatic)
- cryptokernel 8y agoJust leaving this here. Earn crypto by accomplishing tasks in the CoinCircle website. https://coincircle.com/l/50VVxbObg3 https://coincircle.com/l/50VVxbObg3
- 2_listerine_pls 8y agoI wish Google would let individual users upgrade to GSuite Business to try these tools, rather than forcing the whole organization to upgrade. The pricing model is impractical.
- deckar01 8y agoI use GSuite as an individual. I just had to use a personal domain and forward my other account to it.
- 2_listerine_pls 8y agoYeah, but I really want to use it in my organization. It's too expensive to upgrade for a single user.
- seektable 8y agohey you can create pivot tables by BigQuery without paid GSuite subscription at all, this is possible with SeekTable (this is free BI tool): https://www.seektable.com/help/bigquery-pivot-table https://www.seektable.com/help/bigquery-pivot-table
- vvern 8y agoIf you find this interesting you should take a look at https://www.sigmacomputing.com/ https://www.sigmacomputing.com/
- OnlyRepliesToBS 8y ago... and it's discontinued.