Tableplus Bigquery

  1. Tableplus Bigquery Convert

May 28, 2019 What is Python? Well, it is a high-level and general-purpose programming language that can be used for web development, data science and scripting. According to TIOBE index, Python is ranked top 10.

BigQuery is Google's fully managed, petabyte scale, low cost analytics data warehouse. BigQuery is NoOps—there is no infrastructure to manage and you don't need a database administrator—so you can focus on analyzing data to find meaningful insights, use. Bdash - SQL 客户端应用程序,支持 MySQL、 PostgreSQL (Redshift)、 BigQuery。 Base 2 - 一个用于管理 SQLite 数据库的软件。 Chrome MySQL Admin - 一个 Chrome 插件,是 MySQL 开发的跨平台、可视化数据库工具。 Core Data Editor - 核心数据编辑器可让您轻松查看,编辑和分析应用程序的. Apr 05, 2017 The latest Tweets from TablePlus (@TablePlus). Modern, Native database client for Postgres, MySQL, SQLite, SQL Server, Redshift, Redis, CockroachDB and more (macOS. Select the Provider type from the drop-down list. Select the Integrated option to connect to your Amazon Redshift database using OLE DB. Select the Amazon Redshift (x64) option to connect to your Amazon Redshift database using ODBC. Specify the Server name in the appropriate field. You can enter it manually or select it from the drop-down list (if it has been already used).

All of us have at some point worked with some spreadsheet software, like Excel or Google Sheets, or BI tools and we have to admit that they offer certain functionalities that are very handy when it comes to data presentation and reporting, like the so-called pivot tables. Since many business applications require some sort of pivot tables, I am sure many of you have found themselves struggling with how to satisfy these requirements using a database instead of a spreadsheet.

Unfortunately, for many years applying the pivot functionality to data in a database table was cumbersome. The main reason for this is that in a typical relational schema, tables are supposed to grow vertically rather than horizontally, repeating value in the same columns when necessary.

Some may claim that this representation is sufficient -and indeed it is- but having a pivot table that would extend the data across and present metrics in the blink of an eye simplifies comparisons of large amounts of data and filtering on certain attributes’ values.

Table plus bigquery

Lucky for us, during the past few years, some well-known database systems have implemented functions used for pivot table creation, saving us from the development of complicated and “hacky” queries.

At this post, we are going to demonstrate how you can create pivot tables when using PostgreSQL, Redshift or SQL Server. The data we are going to use come from Mixpanel (it is 10 sec work to connect and load your data from Mixpanel to any data warehouse) and represent the number of purchases of 5 different product along different countries.

For convenience, we have constructed a view over the database’s table to have immediate access only to the relevant data, and so our initial table looks like this:

Tableplus Bigquery

So let the pivoting begin!

Since PostgreSQL version 8.3 was released, the crosstab function was introduced that allows users to apply pivoting over some column. In the examples, we are going to use the view’s data from the product catalog and pivot them over the location of the buyer.

Table

However, before moving to the actual query implementation, we need to enable the tablefunc module in our PostgreSQL database that includes, among others, the crosstab function we are going to use.

From the crosstab documentation one can find a few options regarding the function’s options and chose those that fit best his data case.

Table plus bigquery

For our example, the syntax that best fitted the needs was the following:

The SQL parameter produces the source set of data including three columns. The first refers to data that will be presented as rows names, the second to those that will be offered as column names and the last one as the corresponding values.

Using the following query, we can now create a pivot table for the sample data:

Presenting the queries’ results as a pivot table, we obtain the following view. Now, one can easily depict that in Argentina we sold 1 unit of product_1, four units of product_4, etc.

Convert

Another approach, more general and also applicable in most databases including PostgreSQL, Amazon Redshift, and Google BigQuery, involves the use of the CASE clause, as shown in the following example:

For example, in a BigQuery database we would get the same results as before. To prove my point you can see the following table:

All these can also be implemented in MS SQL Server as well using the PIVOT clause which allows us to create a cross tabulation. According to the official documentation, it provides a more straightforward and more readable syntax than the one that may otherwise be specified in a complex series of SELECT…CASE statements.

Over the same initial view as before we can use the following query to create the desired pivot table:

The result we come up to is the same as before.

Overall, it seems that tasks that used to be cumbersome in the past, are now tackled quite easily with the new functionalities that many databases have introduced, including PostgreSQL, Amazon Redshift, Google BigQuery and SQL Server.

Tableplus Bigquery Convert

However, if the pivot is often accessed by multiple users, it would be a good idea to store it as a materialized view so that others users can use the pivot table directly instead of having to manipulate the standard query results.