How to Mine Bitcoin for Free With Google BigQuery

Cryptocurrency miners are always looking for a way to save on electricity costs. Sometimes that involves funding huge mining operations moving to small towns to take advantage of cheap hydro power, or developing obscure mining rig set ups that use Teslas or human body heat as their source of power.

Last week, the Israeli software Uri Shaked kept this proud tradition of weird mining hacks alive by figuring out a way to use Google’s BigQuery to mine Bitcoin for free.

BigQuery is a data analysis tool developed by Google that allows anyone to analyze up to 1 terabyte of data for free each month. According to Shaked’s blog, after he attended a Bitcoin developers meeting in Israel last month, he wondered if he could figure out a way to put his BigQuery skills to use mining Bitcoin.

Before diving into how Shaked made this happen, a little background on Bitcoin. A new block is added to the Bitcoin blockchain every 10 minutes on average, and contains two main types of data: a header and a list of all the Bitcoin transactions that have occurred since the last block. The header consists of 80 bytes of data and mining a Bitcoin block basically involves guessing the right number that results in the correct cryptographic hash of the block header. That four byte number is called a nonce.

This process of finding a nonce is accomplished using a customized computer chip called an ASIC that is optimized to run the Bitcoin hashing algorithm as fast as possible. Although these chips are extremely efficient at mining Bitcoin, the electricity required to power the ASIC is incredibly high. In fact, at this point, a single Bitcoin transaction requires the same amount of energy that is used by an average house in a week. As a result, miners are always on the lookout for cheaper sources of power.

Enter BigQuery, Google’s cloud-based tool for analyzing the contents of massive datasets. The way BigQuery works is a dataset is uploaded to Google’s servers and then a user can run SQL commands—SQL is a programming language designed for manipulating and searching datasets—to understand what’s in the data. As a simple example, say a company had a huge dataset about its customers’ purchases and return history and wanted to know the average amount of time between a purchase and return for a particular item. It could load this data into BigQuery, write these search parameters using SQL and get a fast result.

To figure out how to mine Bitcoin with BigQuery, Shaked first selected a random block from the Bitcoin blockchain and used BigQuery to turn the hex encoded header of the block into binary using the SHA-256 hash function. This proved that he could verify the hashes of Bitcoin blocks using BigQuery, but if he wanted to mine Bitcoin, Shaked had to figure out how to guess the nonce of new blocks, not just verify the nonce for previously mined blocks.

To do this, Shaked first did a test where he tried to reproduce the nonce of a previously mined block. A nonce consists of four bytes and each byte can be represented by a value between 0 and 255. So, Shaked developed a query that would basically search the values 0 through 255 for each of the four bytes until it found the correct combination of four bytes for that block.

Yet as Shaked found, this process was slow. It took about 30 seconds to find a single byte, but if it were trying to find all four correct byte values, it would take about two hours. This is far too slow considering a new Bitcoin block is mined every ten minutes.

As Shaked pointed out, the slowness was a result of joining four separate tables of byte values. A far faster option would be to put every single possible four byte nonce value into a single table and query that one table to try to find the correct value. According to Shaked, for this block there were 4,294,967,296 possible nonce values.

Obviously, he wasn’t about to input over 4 billion values by hand, so instead he found a pre-existing public data set that contained 5.3 billion rows and wrote a script that would systematically try each possible nonce value as it searched across the table. You can visualize this as the query algorithm crawling its way across the table. At each row, it tries a new nonce value, checks if it’s correct, and if not, moves to the next row and tries the next possible nonce value, and so on until the correct value is found.

When Shaked tried this method, however, he received an error message from BigQuery saying that he didn’t have enough memory resources to run that kind of query.

To circumvent this problem, Shaked wrote a query that would guess a random value between 0 and 255 for each of the four byte values as it worked its way across the table with 5.3 billion rows. Sure enough, it worked.

“I was able to check nearly 4 billion hashes in 20 seconds, or about 200 mega-hashes per second,” Shaked wrote on his blog. “That’s not too bad considering GPUs will only get you about 30–60 mega-hashes/second.”

Of course, the current Bitcoin mining difficulty is higher now than for the random block that Shaked had chosen to test his query process. Since the Bitcoin network is designed to add a new block every 10 minutes, as more computing power is added to the network the difficulty of finding a nonce must increase proportionately. This means the difficulty of finding a nonce in the past was easier than it is today and will be in the future. Currently, the number of possible hash combinations is about 2^182, which would take way too long to query using the aforementioned method alone. So Shaked tweaked his approach a bit more and used a 106 billion-row table for his search space.

His best results with this approach was about 500 megahashes per second. Still, he said it may be possible to improve this by running 50 concurrent queries (the maximum allowed on BigQuery) for a theoretical peak hashing rate of 25 gigahashes per second. That’s not bad, but still far slower than dedicated ASIC hardware, which calculates in terms of terahashes per second. To put this in perspective, the most powerful Bitcoin miner’s on the market do about 13 terahashes per second, which is 26,000 times faster than the BigQuery miner’s actual performance, and 520 times faster than its theoretical peak hashing rate of 25 gigahashes per second..

BigQuery allows up to 1 terabyte of data analysis per month and then charges per analyzed byte above that amount. Yet as Shaked pointed out, his approach is technically just generating a large amount of random data using pre-existing tables so as far as BigQuery is concerned, he’s analyzing zero bytes per hash. In other words, this mining method is completely free.

This was just a proof of concept so each query had to be run manually, whereas a dedicated mining operation would be automated. This is probably within the realm of possibility, Shaked wrote, but don’t expect to get rich. If this process was fully automated, it would probably only earn about $5 worth of Bitcoin per year.

“This experiment gave me a whole new perspective about the enormous computing power that is being spent mining Bitcoin,” Shaked said. “We’re talking about 26 billion giga-hashes per second — that is 2.6*10¹⁹ , which is more than the number of grains of sand on earth. Every second. You’ve got to wonder what we’d achieve if we used even a fraction of this computing power for medical research instead…”

If you want the nitty gritty details of how Shaked pulled this off, check out his blog where he breaks it down step by step.