20 ms·

Analyzing Cryptocurrencies Using PostgreSQL

521 points by akulkarni 9y ago

inlineint 9y ago
A nice analysis, and it shows how SQL makes it easy to quickly explore data. However, it seems like the plots of the results of the queries were done manually by writing some code to make each plot. I can't stop but mention that using Apache Zeppelin Notebook [1] with Postgres interpreter for Zeppelin [2] (Spark SQL should provide comparable analytical capabilities as well, but this comment is not about it) it is possible to show graphical representation of query results without writing a single line of code. [1] https://zeppelin.apache.org/ https://zeppelin.apache.org/ [2] https://zeppelin.apache.org/docs/latest/interpreter/postgresql.html https://zeppelin.apache.org/docs/latest/interpreter/postgres...
- sp37344 9y ago
  Author here! For the graphs I used the ggplot library in R, but really any SQL visualization tool (e.g., SQLPad) would have worked as well. Apache Zeppelin Notebook seems like a great tool and I'd be happy to look into it for my future work. Thanks for the recommendation! :)
- placeybordeaux 9y ago
  I've only used zeppelin for spark so far, but it's been a joy to work with.
- krupan 9y ago
  I just started looking for tools like this and came across plotly and dash: https://medium.com/@plotlygraphs/introducing-dash-5ecf7191b503 https://medium.com/@plotlygraphs/introducing-dash-5ecf7191b5... Am I right that zeppelin is similar to this?
- tomerbd 9y ago
  I don't get it, don't you need to write code in Zeppelin to have it show plots? You need to decide which data which aggregation.. do you have a link with any doc showing how to plot without writing code? I followed your links but I miss this.
  - inlineint 9y ago
    Look at this part of the video from https://zeppelin.apache.org https://zeppelin.apache.org: https://www.youtube.com/watch?v=_PQbVH_aO5E&t=3m21s https://www.youtube.com/watch?v=_PQbVH_aO5E&t=3m21s. It shows plotting directly from SQL.
deleted 9y ago
[deleted]
RobAtticus 9y ago
This was work done by our intern over the past few days using TimescaleDB. Our team is around to answer any questions!
cupcakestand 9y ago
OT and hijacking the thread: "TimescaleDB (the OPs product) is a new open source time-series database built up from PostgreSQL." Do you know good alternatives or which distributed databases are generally well suited for huge volumes of time-series data? Cassandra?
- RobAtticus 9y ago
  Obviously I'm biased, but in our testing of TimescaleDB we've found it to do well with large amounts of data. We're still working on benchmarks that we hope to present in the coming weeks/months, but we've been able to sustain high insert rates even when the database contains billions of rows and metrics. And in our preliminary comparisons to Cassandra the query latency for TimescaleDB was much better. Happy to try and address any concerns you may have
  - sroussey 9y ago
    Billions total? Per month? Per day? Per hour? Per minute? I hate the word "large" in these contexts.
    - akulkarni 9y ago
      Our internal benchmarks (which we are publishing soon), show a sustainable insert performance of 100K+ rows/second (where each row contains 10 metrics -- some would call this 1M metrics/second), even at 10 Billion rows. All on a single commodity instance (with only 16GB RAM). (We will publish these soon, along with scripts to reproduce them.) But I agree that "large" often means different things to different people.
- kev009 9y ago
  Most products in this space are pretty obtuse, so I'm excited about TimescaleDB because of the people who started it and the tech it's built on. I was really underwhelmed by problems with Influx, and I don't think this group and this tech will have the same class of issues. I think HBase is better than Cassandra for this workload, and I generally like its design sensibilities and consistency focus (gross oversimplification C* = MySQL, HBase = pgsql?) . OpenTSDB is one of the more mature scale out timeseries DBs and works with both. It works pretty well, but getting a production quality HBase setup is non-trivial. OpenTSDB has some decent built in aggregation and filtering functions, but queries over long time spans are quite slow because it has to pull all the data out of HBase and into the tsd daemon.. that can be fixed with what HBase calls a co-processor (think stored procedure in SQL) but it hasn't been done yet. So, I think TimescaleDB is hitting a real need at the right time, and seeing how expressive and easy to reason those queries are, I am excited to give it a shot.
adamnemecek 9y ago
So what exactly is the current attitude towards cryptocurrencies? E.g. a coin named Diamond went up 200% since yesterday. https://coinmarketcap.com/currencies/diamond/ https://coinmarketcap.com/currencies/diamond/ However it's not obvious what's driving it. Also, on Saturday, there was an ICO of this coin called TenX (https://www.tenx.tech/ https://www.tenx.tech/) which sold 100,000 ETH (~$30M) worth of TenX in like a minute. Is it all pump and dump? I can't imagine that anyone has definite answers but I'm interested to hear some opinions.
- richardwhiuk 9y ago
  They are something between a pyramid scheme and a multi level marketing scheme. They have no inherent worth, are deeply flawed as currencies, and are highly volatile, so aren't useful stores of worth. There is a very low barrier to entry to creating one, so expect them to be created pretty much without limit.
  - adamnemecek 9y ago
    This seems overly pessimistic. They do have inherent value (there's no better way of sending money, no strings attached). I guess the question is whether in the future the world will have one currency or multiple. I also wonder if this will ever be regulated (and if it even can, it seems like a cat-and-mouse game). > They have no inherent worth, They have inherent value as you need some foundation of the internet economy that's ideally not gov't controlled.
    - kweinber 9y ago
      $100 bills also have very little inherent value or worth. They are only useful because people are willing to trade them for goods and services, there is a regulated source of them, and it is clearly defined how to transact with them. Cryptocurrencies have the latter 2 features sewn-up but there aren't a lot of hard-goods out there that are predominantly priced in crytocurrencies. . . until that happens their valuations will remain volatile.
leot 9y ago
Assume near-perfect liquidity among x-coins. Assume, also, that software makes starting a new kind of coin as easy as starting a small business. This implies that the crypto-currency market cap will be divided among far more than "21M". So, given near-perfect liquidity, what makes any particular coin valuable? For shares it's earnings per share. Will we need something akin to "earnings per coin"?
- dvcc 9y ago
  The same thing that makes one fiat currency more valuable than another -- the demand/supply/confidence for that currency.
  - leot 9y ago
    Taxes, central bank, military, and existence of cash all preserve value of fiat. And, further, fiat currencies aren't perfectly liquid with respect to each other.
    - dvcc 9y ago
      Forex markets are basically as liquid as you can get -- of course some exceptions, but I think the idea still holds. While an existing fiat currency might derive its confidence from different sources than that of a digital currency (taxes, military, etc. vs. blockchain, mining, contracts, etc.), it does not make them that different. People still declare the coins value on that confidence/demand.
      - leot 9y ago
        Yes, definitely on the forex thing. But with cryptocurrency-level liquidity there is no reason for any normal consumer to hold funds in any particular crypto currency (or any at all), since they can exchange them cheaply and quickly on demand. That is, unless they're speculating it will increase. But this increase presumes scarcity. And so the question is: whence scarcity?
- ssutch3 9y ago
deleted 9y ago
[deleted]
smaili 9y ago
Slightly off topic, but what are good CoinBase alternatives for buying/selling that cover currencies outside of just Bitcoin, Ethereum and Litecoin?
- hasch 9y ago
  which continent and fiat currency?
  - aryamaan 9y ago
    Asia/INR
- bluetidepro 9y ago
  I've found that Gemini (https://gemini.com https://gemini.com) is the best alternative. You'll see it as most recommendations for alts against Coinbase.
  - smaili 9y ago
    They only seem to support the same currencies as CoinBase.
- linsomniac 9y ago
  Coinbase is what I recommend for getting money into an exchange. CEX.io is another way to get money in, though when you buy bitcoins with them the prices they give you seem a bit low to me. This is easier to get money in by credit card than coinbase, I'd use coinbase if coming from a bank. Bittrex is the exchange I recommend, and they have a lot of currencies. Poloniex is ok, it's big but slightly fewer currencies, I don't like the interface as well. Kraken is a bit better interface, but way fewer currencies. C-CEX has a reputation for scamminess and running questionable currencies, I'd stay away.
- jraines 9y ago
  Bittrex.com is the best I've found.
- 9y ago
gthtjtkt 9y ago
Decent SQL skills for an intern, but I don't really see any "analysis" here. As someone who's been buying and following cryptocurrencies intently for the past few months, I can confidently say there's nothing in this article that even a novice investor would be interested in. The TL;DR is "Prices went up, prices went down. Some more than others." There's nothing actionable in that. And a lot of the currencies in your dataset have basically zero volume. AMIS -- your "most profit in a day" -- appears to have a 24h volume < $1,000 and isn't even traded on a single major exchange. Again, that information serves no purpose, and coins like that should probably be excluded as outliers. The fact that they were not only included but highlighted tells me that the author knows nothing about the market they're attempting to analyze. This looks more like clickbait / advertising, to be honest. "Hey, we need someone to write an article on cryptocurrencies because it's a hot topic. Just give me 2,500 words and a few graphs ASAP. Doesn't matter if it's relevant."
- virtuabhi 9y ago
  There is value in disseminating the information. If you are "buying and following cryptocurrencies intently for the past few months, I can confidently say there's nothing in this article that even a novice investor would be interested in" then please submit the links to your analyses, your data, and your reports so we can learn from your infinite wisdom.
  - gthtjtkt 9y ago
    Do I need to be a Michelin Star chef to point out a turd sandwich?
    - virtuabhi 9y ago
      Are thousands of people enjoying that sandwich? If yes, then you need to backup your claim of "turd sandwich".
- cocochanel 9y ago
  Agree with you, but your comment could be more constructive.
EGreg 9y ago
Where can we get access to this dataset and time series? I would like to run my own analysis too!
- mfreed 9y ago
  There's a link at the bottom of the post for downloading both the dataset and TimescaleDB: https://blog.timescale.com/analyzing-ethereum-bitcoin-and-1200-cryptocurrencies-using-postgresql-downloading-the-dataset-a1bbc2d4d992 https://blog.timescale.com/analyzing-ethereum-bitcoin-and-12... Enjoy!
- deleted 9y ago
  [deleted]
placeybordeaux 9y ago
Props for providing the scraped data set as well!
riston 9y ago
Well written article and great analysis.
spreadstreet 9y ago
If you need additional datasets, head over to https://spreadstreet.io https://spreadstreet.io where you can download over 3,000+ datasets across hundreds of digital currencies. Nice article! Had a good time reading it.
partycoder 9y ago
If you like cryptocurrencies read this: https://www.irs.gov/uac/newsroom/irs-virtual-currency-guidance https://www.irs.gov/uac/newsroom/irs-virtual-currency-guidan...
mrb 9y ago
"Turns out that if you had invested $100 in Bitcoin in July 2010, it would be worth over $5,000,000 today." Actually if you had invested $100 three months earlier, in April 2010 (when BTC started trading at $0.003 on BitcoinMarket.com) it would be worth $87M today.
- mattsouth 9y ago
  Was BitcoinMarket.com the first marketplace? It struck me on reading that quote that I had no idea how to purchase bitcoin in 2010, and was more interested in finding out how to mine it.
  - mrb 9y ago
    Yes it was the very first.
irrational 9y ago
I remember when bit-currency first came out years ago. Back then it was still possible to generate them on your slow desktop machine without too much work. I considered doing so, but figured they would never go anywhere so why bother. Sigh...
mbonzo 9y ago
In case anybody here finds this useful, I made a screencast following the blog for setting up and loading the data: https://www.youtube.com/watch?v=RSFC24FMxy4&feature=youtu.be https://www.youtube.com/watch?v=RSFC24FMxy4&feature=youtu.be
- mbonzo 9y ago
  Here's pt. 2 of the screencast: https://youtu.be/LrBAWL6bYa4 https://youtu.be/LrBAWL6bYa4
mbonzo 9y ago
In case anybody here finds this useful, I made a screencast following the blog for setting up and loading the data: https://www.youtube.com/watch?v=RSFC24FMxy4&feature=youtu.be https://www.youtube.com/watch?v=RSFC24FMxy4&feature=youtu.be

521 points by akulkarni 9y ago

A nice analysis, and it shows how SQL makes it easy to quickly explore data. However, it seems like the plots of the results of the queries were done manually by writing some code to make each plot. I can't stop but mention that using Apache Zeppelin Notebook [1] with Postgres interpreter for Zeppelin [2] (Spark SQL should provide comparable analytical capabilities as well, but this comment is not about it) it is possible to show graphical representation of query results without writing a single line of code. [1] https://zeppelin.apache.org/ https://zeppelin.apache.org/ [2] https://zeppelin.apache.org/docs/latest/interpreter/postgresql.html https://zeppelin.apache.org/docs/latest/interpreter/postgres...

sp37344 9y ago
Author here! For the graphs I used the ggplot library in R, but really any SQL visualization tool (e.g., SQLPad) would have worked as well. Apache Zeppelin Notebook seems like a great tool and I'd be happy to look into it for my future work. Thanks for the recommendation! :)
placeybordeaux 9y ago
I've only used zeppelin for spark so far, but it's been a joy to work with.
krupan 9y ago
I just started looking for tools like this and came across plotly and dash: https://medium.com/@plotlygraphs/introducing-dash-5ecf7191b503 https://medium.com/@plotlygraphs/introducing-dash-5ecf7191b5... Am I right that zeppelin is similar to this?
tomerbd 9y ago
I don't get it, don't you need to write code in Zeppelin to have it show plots? You need to decide which data which aggregation.. do you have a link with any doc showing how to plot without writing code? I followed your links but I miss this.
- inlineint 9y ago
  Look at this part of the video from https://zeppelin.apache.org https://zeppelin.apache.org: https://www.youtube.com/watch?v=_PQbVH_aO5E&t=3m21s https://www.youtube.com/watch?v=_PQbVH_aO5E&t=3m21s. It shows plotting directly from SQL.

deleted 9y ago

[deleted]

RobAtticus 9y ago

This was work done by our intern over the past few days using TimescaleDB. Our team is around to answer any questions!

cupcakestand 9y ago

OT and hijacking the thread: "TimescaleDB (the OPs product) is a new open source time-series database built up from PostgreSQL." Do you know good alternatives or which distributed databases are generally well suited for huge volumes of time-series data? Cassandra?

RobAtticus 9y ago
Obviously I'm biased, but in our testing of TimescaleDB we've found it to do well with large amounts of data. We're still working on benchmarks that we hope to present in the coming weeks/months, but we've been able to sustain high insert rates even when the database contains billions of rows and metrics. And in our preliminary comparisons to Cassandra the query latency for TimescaleDB was much better. Happy to try and address any concerns you may have
- sroussey 9y ago
  Billions total? Per month? Per day? Per hour? Per minute? I hate the word "large" in these contexts.
  - akulkarni 9y ago
    Our internal benchmarks (which we are publishing soon), show a sustainable insert performance of 100K+ rows/second (where each row contains 10 metrics -- some would call this 1M metrics/second), even at 10 Billion rows. All on a single commodity instance (with only 16GB RAM). (We will publish these soon, along with scripts to reproduce them.) But I agree that "large" often means different things to different people.
kev009 9y ago
Most products in this space are pretty obtuse, so I'm excited about TimescaleDB because of the people who started it and the tech it's built on. I was really underwhelmed by problems with Influx, and I don't think this group and this tech will have the same class of issues. I think HBase is better than Cassandra for this workload, and I generally like its design sensibilities and consistency focus (gross oversimplification C* = MySQL, HBase = pgsql?) . OpenTSDB is one of the more mature scale out timeseries DBs and works with both. It works pretty well, but getting a production quality HBase setup is non-trivial. OpenTSDB has some decent built in aggregation and filtering functions, but queries over long time spans are quite slow because it has to pull all the data out of HBase and into the tsd daemon.. that can be fixed with what HBase calls a co-processor (think stored procedure in SQL) but it hasn't been done yet. So, I think TimescaleDB is hitting a real need at the right time, and seeing how expressive and easy to reason those queries are, I am excited to give it a shot.

adamnemecek 9y ago

So what exactly is the current attitude towards cryptocurrencies? E.g. a coin named Diamond went up 200% since yesterday. https://coinmarketcap.com/currencies/diamond/ https://coinmarketcap.com/currencies/diamond/ However it's not obvious what's driving it. Also, on Saturday, there was an ICO of this coin called TenX (https://www.tenx.tech/ https://www.tenx.tech/) which sold 100,000 ETH (~$30M) worth of TenX in like a minute. Is it all pump and dump? I can't imagine that anyone has definite answers but I'm interested to hear some opinions.

richardwhiuk 9y ago
They are something between a pyramid scheme and a multi level marketing scheme. They have no inherent worth, are deeply flawed as currencies, and are highly volatile, so aren't useful stores of worth. There is a very low barrier to entry to creating one, so expect them to be created pretty much without limit.
- adamnemecek 9y ago
  This seems overly pessimistic. They do have inherent value (there's no better way of sending money, no strings attached). I guess the question is whether in the future the world will have one currency or multiple. I also wonder if this will ever be regulated (and if it even can, it seems like a cat-and-mouse game). > They have no inherent worth, They have inherent value as you need some foundation of the internet economy that's ideally not gov't controlled.
  - kweinber 9y ago
    $100 bills also have very little inherent value or worth. They are only useful because people are willing to trade them for goods and services, there is a regulated source of them, and it is clearly defined how to transact with them. Cryptocurrencies have the latter 2 features sewn-up but there aren't a lot of hard-goods out there that are predominantly priced in crytocurrencies. . . until that happens their valuations will remain volatile.

leot 9y ago

Assume near-perfect liquidity among x-coins. Assume, also, that software makes starting a new kind of coin as easy as starting a small business. This implies that the crypto-currency market cap will be divided among far more than "21M". So, given near-perfect liquidity, what makes any particular coin valuable? For shares it's earnings per share. Will we need something akin to "earnings per coin"?

dvcc 9y ago
The same thing that makes one fiat currency more valuable than another -- the demand/supply/confidence for that currency.
- leot 9y ago
  Taxes, central bank, military, and existence of cash all preserve value of fiat. And, further, fiat currencies aren't perfectly liquid with respect to each other.
  - dvcc 9y ago
    Forex markets are basically as liquid as you can get -- of course some exceptions, but I think the idea still holds. While an existing fiat currency might derive its confidence from different sources than that of a digital currency (taxes, military, etc. vs. blockchain, mining, contracts, etc.), it does not make them that different. People still declare the coins value on that confidence/demand.
    - leot 9y ago
      Yes, definitely on the forex thing. But with cryptocurrency-level liquidity there is no reason for any normal consumer to hold funds in any particular crypto currency (or any at all), since they can exchange them cheaply and quickly on demand. That is, unless they're speculating it will increase. But this increase presumes scarcity. And so the question is: whence scarcity?
ssutch3 9y ago

deleted 9y ago

[deleted]

smaili 9y ago

Slightly off topic, but what are good CoinBase alternatives for buying/selling that cover currencies outside of just Bitcoin, Ethereum and Litecoin?

hasch 9y ago
which continent and fiat currency?
- aryamaan 9y ago
  Asia/INR
bluetidepro 9y ago
I've found that Gemini (https://gemini.com https://gemini.com) is the best alternative. You'll see it as most recommendations for alts against Coinbase.
- smaili 9y ago
  They only seem to support the same currencies as CoinBase.
linsomniac 9y ago
Coinbase is what I recommend for getting money into an exchange. CEX.io is another way to get money in, though when you buy bitcoins with them the prices they give you seem a bit low to me. This is easier to get money in by credit card than coinbase, I'd use coinbase if coming from a bank. Bittrex is the exchange I recommend, and they have a lot of currencies. Poloniex is ok, it's big but slightly fewer currencies, I don't like the interface as well. Kraken is a bit better interface, but way fewer currencies. C-CEX has a reputation for scamminess and running questionable currencies, I'd stay away.
jraines 9y ago
Bittrex.com is the best I've found.
9y ago

gthtjtkt 9y ago

Decent SQL skills for an intern, but I don't really see any "analysis" here. As someone who's been buying and following cryptocurrencies intently for the past few months, I can confidently say there's nothing in this article that even a novice investor would be interested in. The TL;DR is "Prices went up, prices went down. Some more than others." There's nothing actionable in that. And a lot of the currencies in your dataset have basically zero volume. AMIS -- your "most profit in a day" -- appears to have a 24h volume < $1,000 and isn't even traded on a single major exchange. Again, that information serves no purpose, and coins like that should probably be excluded as outliers. The fact that they were not only included but highlighted tells me that the author knows nothing about the market they're attempting to analyze. This looks more like clickbait / advertising, to be honest. "Hey, we need someone to write an article on cryptocurrencies because it's a hot topic. Just give me 2,500 words and a few graphs ASAP. Doesn't matter if it's relevant."

virtuabhi 9y ago
There is value in disseminating the information. If you are "buying and following cryptocurrencies intently for the past few months, I can confidently say there's nothing in this article that even a novice investor would be interested in" then please submit the links to your analyses, your data, and your reports so we can learn from your infinite wisdom.
- gthtjtkt 9y ago
  Do I need to be a Michelin Star chef to point out a turd sandwich?
  - virtuabhi 9y ago
    Are thousands of people enjoying that sandwich? If yes, then you need to backup your claim of "turd sandwich".
cocochanel 9y ago
Agree with you, but your comment could be more constructive.

EGreg 9y ago

Where can we get access to this dataset and time series? I would like to run my own analysis too!

mfreed 9y ago
There's a link at the bottom of the post for downloading both the dataset and TimescaleDB: https://blog.timescale.com/analyzing-ethereum-bitcoin-and-1200-cryptocurrencies-using-postgresql-downloading-the-dataset-a1bbc2d4d992 https://blog.timescale.com/analyzing-ethereum-bitcoin-and-12... Enjoy!
deleted 9y ago
[deleted]

placeybordeaux 9y ago

Props for providing the scraped data set as well!

riston 9y ago

Well written article and great analysis.

spreadstreet 9y ago

If you need additional datasets, head over to https://spreadstreet.io https://spreadstreet.io where you can download over 3,000+ datasets across hundreds of digital currencies. Nice article! Had a good time reading it.

partycoder 9y ago

If you like cryptocurrencies read this: https://www.irs.gov/uac/newsroom/irs-virtual-currency-guidance https://www.irs.gov/uac/newsroom/irs-virtual-currency-guidan...

mrb 9y ago

"Turns out that if you had invested $100 in Bitcoin in July 2010, it would be worth over $5,000,000 today." Actually if you had invested $100 three months earlier, in April 2010 (when BTC started trading at $0.003 on BitcoinMarket.com) it would be worth $87M today.

mattsouth 9y ago
Was BitcoinMarket.com the first marketplace? It struck me on reading that quote that I had no idea how to purchase bitcoin in 2010, and was more interested in finding out how to mine it.
- mrb 9y ago
  Yes it was the very first.

irrational 9y ago

I remember when bit-currency first came out years ago. Back then it was still possible to generate them on your slow desktop machine without too much work. I considered doing so, but figured they would never go anywhere so why bother. Sigh...

mbonzo 9y ago

In case anybody here finds this useful, I made a screencast following the blog for setting up and loading the data: https://www.youtube.com/watch?v=RSFC24FMxy4&feature=youtu.be https://www.youtube.com/watch?v=RSFC24FMxy4&feature=youtu.be

mbonzo 9y ago
Here's pt. 2 of the screencast: https://youtu.be/LrBAWL6bYa4 https://youtu.be/LrBAWL6bYa4

mbonzo 9y ago