BigQuery JDBC connector for Tableau takes too long to create an extract

[nicolas-ilr]

Hello,

I'm trying to create an extract in Tableau using the BigQuery JDBC connector. I can successfully connect via the connector to my BigQuery dataset, but the problem is when trying to create an extract, it is taking too long. I started the extract creation about an hour ago and it is showing the message box:
"Creating Extract
Importing data
Rows retrieved: {Increasing}
"
After one hour it is currently at 4.7 million rows. When checking in BigQuery, the query was esecuted in just a few seconds.
I'm trying to create the extract through Tableau Desktop in my PC.

It seems like it is too long, has anyone got any insight into what could be going on and if/how this can be improved? I need to get like 50M rows.

Axny help is appreciated, thanks!

[ms4446]

There are several ways to optimize the performance of the BigQuery JDBC connector with Tableau.

1. Batch Operations: One way to improve the JDBC performance is by using batch operations. This involves reading your big query and storing the results in some kind of buffer. When the buffer is full, you should run a subquery for all data collected in the buffer. This significantly reduces the number of SQL statements to execute​.

2. Using Efficient Maps: If your records are not so big, you can store them all at once even for a large table. This approach may need much heap memory (i.e. 100 MB - 1 GB) - but it is much faster than the batch operation approach. To do this, you need an efficient map implementation, for example, gnu.trove.map.TIntObjectMap which is much better than Java's standard library maps​. For more details please refer to this Stack overflow post

3. Customizing Your JDBC Data Connection: Tableau supports the ability to customize your JDBC data connection, which can improve the connection experience. This can be done using a Tableau Datasource Customization (TDC) file or a PROPERTIES file​. Please refer to the Tableau Documentation

   • TDC File: A TDC file is an XML file with a ".tdc" filename extension. These files are used to customize Tableau-specific settings. For the Other Databases (JDBC) connector, the class name is "genericjdbc". Tableau uses only the customizations it defines and not any defined in the TDC file​.

   • PROPERTIES File: PROPERTIES files are used by JDBC and passed directly through to the JDBC driver. They are plain-text files with a ".properties" filename extension. They contain key-value pairs for each connection parameter. A PROPERTIES file should be in Latin-1 format. However, as long as it includes only ASCII characters, you can safely save the file in UTF-8 format without a BOM (byte order mark)​. Please refer to the Tableau documentation for more details.