3 ms·

Could you name some better alternatives for analytical db?

DeathArrow 7y ago
We use Teradata for our data warehouse. We have an enormous quantity of data in there (We are a multinational wholesale chain). We however plan to move to either BigQuery or an inhouse solution in the future. But for now Teradata is our workhorse and quite good at what it does.
bertil 7y ago
There are a lot of commercial solution that are geared towards reporting, from old-school Oracle, TeraData even SAS (would not recommend that one). Some options that came our recently when “Big Data” was a sentence people said: Hive, RedShift, S3, Cassandra. New players that abstract a lot of the problems from the others and add Machine-Learning capacities on top: Google BigQuery, Snowflake. There are non-tabular structure like the graph based ones, Neo4j e.g.: I’ve yet to find a business where that’s a viable option for analytics for the first five years but I like that those are bringing something new. Most of those are great and work: they have connectors to whatever language or tool you want to use. The most promising tool you’ll need to handle most of the transformation is either AirFlow or preferably dbt. Those are is independent from the database, so don’t worry too much about the features that vendors tout. One key thing: monitor all the queries going to that database, find the expensive ones, those with suspicious patterns, etc. Spin-up, response time can matter, but they are rarely a problem for most “slow” analytic use cases; for instance, Google BigQuery takes 10 seconds no matter what you query and it’s fine. On the other hand, concurrency has been an issue for me more than anything: all the analysts and managers trying to update their dashboards on Monday at 10 am. You rapidly get to a point where prices are high and negotiated, so you want to think about your likely usage in the next years before you step into that meeting. Key decision: by default, prefer the tech that is closest to the rest of your stack because ingress is the easiest factor to predict. Looker will be mentioned: it’s on top of all that, downstream from AirFlow/dbt.
mountainview 7y ago
Shameless plug: You might be interested in TiDB (https://github.com/pingcap/tidb https://github.com/pingcap/tidb), an open-source distributed HTAP database. - For OLTP workload and some ad-hoc queries, you can use TiDB + TiKV. One of our adopters has a production cluster with 300+ TB data and can easily cope with the spike caused by the brought by COVID-19. - For more complex queries, TiSpark+ TiKV might work well; for heavier queries, we added a columnar store, TiFlash, see https://pingcap.com/blog/delivering-real-time-analytics-and-true-htap-by-combining-columnstore-and-rowstore/ https://pingcap.com/blog/delivering-real-time-analytics-and-...
jacques_chester 7y ago
There are a lot of products that either fork or directly extend PostgreSQL for OLAP workloads. Greenplum[0], Materialize[1], Citus[2], PipelineDB[3] etc etc. [0] https://greenplum.org/ https://greenplum.org/ [1] https://materialize.io/ https://materialize.io/ [2] https://www.citusdata.com/ https://www.citusdata.com/ (edit: I said TimescaleDB, I was thinking of Citus) [3] https://github.com/pipelinedb/pipelinedb https://github.com/pipelinedb/pipelinedb Disclosure: I work for VMware, which sponsors Greenplum development and sells commercial offerings.

We use Teradata for our data warehouse. We have an enormous quantity of data in there (We are a multinational wholesale chain). We however plan to move to either BigQuery or an inhouse solution in the future. But for now Teradata is our workhorse and quite good at what it does.

bertil 7y ago

There are a lot of commercial solution that are geared towards reporting, from old-school Oracle, TeraData even SAS (would not recommend that one). Some options that came our recently when “Big Data” was a sentence people said: Hive, RedShift, S3, Cassandra. New players that abstract a lot of the problems from the others and add Machine-Learning capacities on top: Google BigQuery, Snowflake. There are non-tabular structure like the graph based ones, Neo4j e.g.: I’ve yet to find a business where that’s a viable option for analytics for the first five years but I like that those are bringing something new. Most of those are great and work: they have connectors to whatever language or tool you want to use. The most promising tool you’ll need to handle most of the transformation is either AirFlow or preferably dbt. Those are is independent from the database, so don’t worry too much about the features that vendors tout. One key thing: monitor all the queries going to that database, find the expensive ones, those with suspicious patterns, etc. Spin-up, response time can matter, but they are rarely a problem for most “slow” analytic use cases; for instance, Google BigQuery takes 10 seconds no matter what you query and it’s fine. On the other hand, concurrency has been an issue for me more than anything: all the analysts and managers trying to update their dashboards on Monday at 10 am. You rapidly get to a point where prices are high and negotiated, so you want to think about your likely usage in the next years before you step into that meeting. Key decision: by default, prefer the tech that is closest to the rest of your stack because ingress is the easiest factor to predict. Looker will be mentioned: it’s on top of all that, downstream from AirFlow/dbt.

mountainview 7y ago

Shameless plug: You might be interested in TiDB (https://github.com/pingcap/tidb https://github.com/pingcap/tidb), an open-source distributed HTAP database. - For OLTP workload and some ad-hoc queries, you can use TiDB + TiKV. One of our adopters has a production cluster with 300+ TB data and can easily cope with the spike caused by the brought by COVID-19. - For more complex queries, TiSpark+ TiKV might work well; for heavier queries, we added a columnar store, TiFlash, see https://pingcap.com/blog/delivering-real-time-analytics-and-true-htap-by-combining-columnstore-and-rowstore/ https://pingcap.com/blog/delivering-real-time-analytics-and-...

jacques_chester 7y ago

There are a lot of products that either fork or directly extend PostgreSQL for OLAP workloads. Greenplum[0], Materialize[1], Citus[2], PipelineDB[3] etc etc. [0] https://greenplum.org/ https://greenplum.org/ [1] https://materialize.io/ https://materialize.io/ [2] https://www.citusdata.com/ https://www.citusdata.com/ (edit: I said TimescaleDB, I was thinking of Citus) [3] https://github.com/pipelinedb/pipelinedb https://github.com/pipelinedb/pipelinedb Disclosure: I work for VMware, which sponsors Greenplum development and sells commercial offerings.