Google makes BigQuery available in Sheets

https://techcrunch.com/2019/04/10/google-makes-the-power-of-bigquery-available-in-sheets/

by truth_seeker • 7 years ago

345 121 7 years ago

All 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• 7 years ago

Try this:

http://wordgrabbag.com/

Here is Google's Patent:

http://patft.uspto.gov/netacgi/nph-Parser?OS=pn/7,350,187&RS...

appleflaxen• 7 years ago

that'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• 7 years ago

What's a use case for this?

mej10• 7 years ago

I wonder how enforceable that patent is.

londons_explore• 7 years ago

I 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• 7 years ago

Have a look at https://projector.tensorflow.org/

hnuser123456• 7 years ago

Still 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• 7 years ago

That’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.

polskibus6• 7 years ago

How's bigquery performance in comparison to clickhouse? When is it worth switching from one to the other?

quantumcd• 7 years ago

Not sure you can compare them as BigQuery is serverless/SaaS. Guess it is more of a question of if you want to run it yourself.

wikibob• 7 years ago

BQ is considerably more performant.

msquog• 7 years ago

This is uninformed and unsubstantiated.

The answer depends massively on the intended use case.

dajonker2• 7 years ago

If you use is only to run 4 different queries on NYC taxi data, they are about similar: https://tech.marksblogg.com/benchmarks.html

Otherwise it's really difficult to compare without knowing how much data you have, how often you query, etc.

filimonov• 7 years ago

For ClickHouse speed and performance is #1 priority, so it beats most of the other databases.

So as an in-house solution, ClickHouse most probably would be the fastest option (if your use case suits OLAP requirements).

For clouds / PaaS - it's hard to compare directly. Do you know how many servers will process your BigQuery request? AFAIK usually BigQuery shows a bit higher performance than single mid-level ClickHouse server (but you can also have a cluster of ClickHouse servers).

crystaln• 7 years ago

Cool. 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?

mda2• 7 years ago

This is getting tired and adds nothing to the discussion.

coldtea• 7 years ago

If one is not familiar with Google's background on the matter, it's the number one thing they should know.

danols• 7 years ago

Feel free to list the Google Cloud products that they have shut down as part of your argument. A better argument would be that you will do not trust them not to do a massive price increase when they become dominant enough with a product. Like they did with the Maps API.

SquareWheel• 7 years ago

I largely ignore Google products announcements these days.

Well clearly not...

polskibus6• 7 years ago

How 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• 7 years ago

We'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.

polskibus6• 7 years ago

That 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.

dajonker2• 7 years ago

That depends on how many columns your pivot table uses. Less columns = less data = lower price. If the implementation is smart enough it could use BigQuery partitions for further cost savings.

danielecook2• 7 years ago

Bigquery also uses caching which presumably is used here.

polskibus6• 7 years ago

Is the data size calculated based on raw data on which queries operate (for example all transactions with amount column), or on query result size (for example sum(amount) -single row) ?

noer2• 7 years ago

I had used Data Studio to provide visualizations of data I had in BQ. I wasn't able to create anything useful without creating a bunch of custom views in BQ. Data Studio seemed to refresh the data a few times a day, which queried the BQ views . It ended up being annoying enough from a pricing perspective that I stopped using Data Studio.

havermeyer2• 7 years ago

Give BI Engine a try! https://cloud.google.com/bi-engine/docs/. The model is that you pay for storage--currently free during the beta--but any queries from Data Studio don't incur costs. It was just announced during Next this week.

As a disclaimer, I'm an engineer on the BigQuery team (but not BI Engine specifically).

danielecook2• 7 years ago

I’ve had trouble getting more than 10k rows in google sheets. It will be interesting to see how well this works.

ramraj072• 7 years ago

I 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• 7 years ago

Have to ask, what is the use for such a Frankenstein?

richjdsmith• 7 years ago

Boy, 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?

seektable2• 7 years ago

well, 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.

paulcole2• 7 years 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• 7 years ago

https://9to5google.com/2019/04/08/google-duplex-expansion-pi... says it's been live and expanding.

jasonvorhe2• 7 years ago

Why do you spread such negativity if you haven't even fact checked that what you're saying is true?

lopuhin2• 7 years ago

That'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.

paulcole2• 7 years ago

I was spreading a quote from the article.

cosmie• 7 years ago

Similar 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-ex...

mritchie712• 7 years ago

This was one of the things I missed when moving from Excel to Sheets and led to me building 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.

wodenokoto3• 7 years ago

Googles 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-bi...

lmkg2• 7 years ago

That 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.

dalbasal2• 7 years ago

For 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• 7 years ago

Earlier 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/110653418815977881...

zikzak• 7 years ago

This 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".

munchbunny3• 7 years ago

This is disappointingly true.

You'd think we would have come up with better answers. I'm constantly looking for ways to better manage bug tracking, todo lists, and even managing job searches and professional networking, because I'm still not really happy with anything I've found.

As I bounce back and forth between different tools, I always land back on just using Excel or Google Sheets. I'm never entirely happy with those either, but I find that they're no worse at the job than the dedicated tools.

mises3• 7 years ago

How very true. I know almost every person who uses Excel on a regular basis would kill you if you tried to take it away (I heard one proposed change to openoffice for price reasons; no more).

And I have found keeping a todo.txt file on my desktop is still better than any productivity software. Although, these days, it's a symlink to a cloud-storage folder on my various machines.

irrational• 7 years ago

And here I was thinking that only I emailed things to myself all the time. Its true that there really is nothing that I've found to be easier than emailing things to myself.

patkai• 7 years ago

Meaning just how awesome email and Excel are!

hcnews2• 7 years ago

Interesting, I don't relate much to either of the statement (not that my experience negates its applicability for other people).

On a bit of thought, I find it useful to organize and break down things and maintain lists (the daily/weekly list habit works best for me currently). This is different than emailing things to myself because I maintain an actively curated list that's hopefully logically organized in big and small tasks.

On the enterprise software front, I am a linux-friendly software engineer, so commandline tools are my best friend. I am not sure Excel can ever beat the productivity of vim+linux tools, let alone other specialized tools like sql. One thing to note is that a lot of time I deal with production data, so its a huge effort to actually move data to excel.

duxup• 7 years ago

I end up outputting a great deal of data to spreadsheets simply because a lot of users who want to work with data know how to do it in a spreadsheet.

And really I don't mind as some of those users are really good at it.

snthpy2• 7 years ago

Anyone know of a tool that can extract the calculation/dependency DAG from an Excel workbook or Google sheet?

Ideally I'd like a Python package that I can point at an .xlsx file and it would give me back an object graph that replicates the values and calculations of the workbook.

prepend2• 7 years ago

Excel is just a zip of xml so your graph is in there already. Just xslt it to whatever format you prefer.

haney• 7 years ago

https://formulas.readthedocs.io/en/stable/

lucasverra• 7 years ago

They are a very reslilient paradigm.

I've done some tech consulting work and we found a way to deal with this, even at a "traditional" SME level.

The answer :

enter excel (not csv) -> decode data with sheetJS into web app + web frontend for "make the stuff happen" -> export to excel with sheetJS (not csv).

MY 2 cts interpretation : everyone uses excel because everyone uses excel. Above workflow does not raise any barrier for client and he's happy with time performance gains

savrajsingh• 7 years ago

Last 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• 7 years ago

What tool did you use?

faizshah• 7 years ago

Yea 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• 7 years ago

Check out the newly announced data fusion

falcor84• 7 years ago

I'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• 7 years ago

Yes, 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.

mises3• 7 years ago

I 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.

SquishyPanda232• 7 years ago

Finance guys and gals have shells like R or Python.

Spreadsheets are GUIs for people who prefer that to shells.

mises3• 7 years ago

Don'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.

hn_throwaway_99• 7 years ago

I 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• 7 years ago

Exactly. 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.

burtonator• 7 years ago

I used to hate on spreadsheets because when I was in IT as a kid I had to install Excel and Office and it really sucked back then.

I didn't understand why finance liked them so much and I just looked upon finance groups as boring old finance nerds.

Man was I wrong... I love Sheets and spreadsheets in general.

I've literally spent hours in Sheets running financial analysis of our operational stack and saved thousands and thousands of dollars a month by analyzing our hardware spend.

davemp• 7 years ago

My hate for spreadsheets come from working in institutions that copy/paste around hideous VBA infested sheets for metrics/reporting instead of using a database.

There is a lot of painful excel abuse in business.

alexhutcheson• 7 years ago

Most of the time that happens because the internal tools/databases built or maintained by IT are unusable or just don't do the necessary reporting. Most companies don't have in-house dev teams that are capable of building usable business-relevant tools.

closeparen• 7 years ago

Shell is a great way to think about it: bash scripts mutating flat files can be reasonable for a personal adhoc task, and maybe even share for reusability, but you don’t want to find them at the core of a backend architecture where an RDBMS ought to be.

ozzyman700• 7 years ago

They should be treated like jupytr notebooks, used for exploration but productionalizing excel workflows is not fun at all especially if people have gotten into the ActiveX/VBA control stuff.

Similar to MS Access or other low code environments, it is great until it is absolutely unacceptable due to system limitations.

minimaxir2• 7 years ago

It'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-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)

2_listerine_pls2• 7 years ago

I 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• 7 years ago

I use GSuite as an individual. I just had to use a personal domain and forward my other account to it.

2_listerine_pls2• 7 years ago

Yeah, but I really want to use it in my organization. It's too expensive to upgrade for a single user.

seektable2• 7 years ago

hey 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

vvern• 7 years ago

If you find this interesting you should take a look at https://www.sigmacomputing.com/

Google makes BigQuery available in Sheets

Related Stories