Struggling to connect bigquery data sources to quicksight. The integration doesn’t seem very clean. Was able to ‘connect’ bigquery and see what datasets we have available in bigquery, but then QS isn’t able to load any tables and gives a cryptic error as to why tables cannot be displayed. I was able to query the table I need directly w/ custom SQL in quicksight, but then all number values are treated as null so I can’t build any visualizations so it’s a super strange issue. I’m not sure what the best route is, but I may need support from AWS directly.
1 Like
Hello @nlisauskas, welcome to the QuickSight community!
Honestly, I have always guided my clients bring data in through an alternative datasource rather than using BigQuery because of the issues I have seen utilizing it. My recommendation would be to move the data to S3 and bring it into QuickSight through Athena instead.
It is possible though that there was an error with setting up the datasource so I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case.
If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf.
I believe discussing this with support is likely the best way to resolve this issue. Let me know if you have any further questions, otherwise I can archive this topic for our support team as well. Thank you!
Thanks for your reply. Will try connecting with support… what types of issues have you seen when utilizing BigQuery in Quicksight?
1 Like
Hello @nlisauskas, I don’t have a specific example at the moment but it mostly comes down to the fact that Athena, the AWS alternative, was built to work cohesively with QuickSight. Since both are AWS products, they integrate seamlessly and therefore you run into less limitations and connection issues. Plus, you are able to keep the billing for your analytics related products within a single source, rather than splitting between AWS and GCP.
While that is my preference, your connection should work. Let me know if you are able to work out a solution with the support team, or let me know if you have any remaining questions and I can guide you further. Thank you!
Hi @nlisauskas can you create an AWS support ticket so our team can look into this? Numeric values are supported in the connector so we would like to understand what is causing the issue.
Can you please navigate me to how to create a support ticket? Finding it super confusing and don’t think I was able to successfully create one the first time I tried.
@vignessh.b any ideas here? Is there anyway you and I could review together directly?
1 Like
Hello @nlisauskas, were you able to follow the link I shared above to open a support ticket? The documentation will walk you through the steps to create one. I can link it again below to guide you:
No. We do not have anything above Basic Support on our account and to upgrade support would be very costly at this stage for the company based on our calculations of 3% of monthly spend. Hoping to just get a solution via forum if at all possible as the issue seems fairly trivial.
Hello @nlisauskas, maybe with some more information, I can try to assist you further.
How are you bringing the tables into QuickSight?
- Direct Query or Spice
- Custom SQL or Directly querying data from BigQuery
- Are you running any joins on the table in QuickSight?
What is the cryptic error you are seeing when trying to ingest the data?
Is it showing the data as you are expecting in the dataset creation tab and then failing when you hit save and publish? Also, are any of your fields certain datatype values that are not expected in QuickSight?
Let me know some more information about what is happening, and I can try to help. Thank you!
I’ve tried both direct query & SPICE & both custom sql / directly querying from BigQuery. No joins.
The data types that are not working are ‘numeric’ and show just fine when I am evaluating the dataset but not working after I ‘save & publish’. The numeric values are juts all null in any type of analysis I try to build even if not null in the dataset before hitting save & publish.
The cryptic error is:
Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again.
region:
us-east-1
timestamp:
1715608216021
requestId:
f61237dc-979f-44fa-b503-035c6868a216
sourceErrorCode:
100032
sourceErrorMessage:
[Simba]BigQueryJDBCDriver Error executing query job. Message: Syntax error: Missing whitespace between literal and alias at [1:42]
sourceErrorState:
HY000
sourceException:
java.sql.SQLException
sourceType:
BIGQUERY
1 Like
Hello @nlisauskas, out of curiosity, what happens if you manually change every column that is returning nulls to a string datatype, then try importing it again? I am interested if it would return something other than NULLs.
Same thing unfortunately; originally they were string data type and then I cast them to numeric and issue still occurs.
Hello @nlisauskas, I don’t believe I am able to assist you further with this issue. To try and provide some information about this issue, I will tag this topic appropriately and archive it for our support team. If you are able to get further along in the process and need further assistance, please post a new topic in the community. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. For now, my best suggestion would be to find an alternate way to bring your data into QuickSight since you are not able to submit a support ticket. Thank you!