Hey there!
I’m hoping that someone with experience using the MongoDB BI connector and Tableau will be able to help me out. I am totally lost and don’t have any idea how to debug this issue I’m having with the BI connector.
Currently I am running both MongoDB and Tableau inside of a kubernetes cluster. Inside the cluster I also have a pod running the BI Connector. Tableau is successfully connecting to MongoDB via the BI connector and I am able to create workbooks and visualizations with multiple collections from MongoDB inside of Tableau.
Where my problem starts is that some large queries simply hang and never complete or give any errors. I’ve noticed this in Tableau as well as via a mysql cli client I have connected to the BI connector. In both cases the BI connector never completes the request. I know that the query has valid SQL syntax so I am totally stumped here.
Is there perhaps some kind of limitation that I am facing because I am not using MongoDB’s Atlas product?
I will include some queries that definitely work and the query that does not work. Any help would be so greatly appreciated. Or if anyone has any insight as to what may cause the long hang.
Below are two queries that work fine:
SELECT
market,
CONVERT(date, date) as date,
clicks,
conversions,
cost,
impressions
FROM ad_metrics
SELECT
go.customer_id,
go.id as lead_id,
CONVERT(DATE_SUB(go.created_at, INTERVAL 7 HOUR), date) as date,
go.market as market,
go.make,
go.model,
go.no_way,
go.repair_location,
go.source as website_source,
go.utm_content,
go.utm_source,
go.post_tax_amount_requested
FROM god_objects go
WHERE (go.is_a_test = 0 OR go.is_a_test IS NULL)
AND (go.carparts = 0 OR go.carparts IS NULL)
AND (go.no_way = 0 OR go.no_way IS NULL)
and below is the query that hangs:
WITH tableau_ads as (
SELECT
market,
CONVERT(date, date) as date,
clicks,
conversions,
cost,
impressions
FROM ad_metrics
), tableau_leads as (
SELECT
go.customer_id,
go.id as lead_id,
CONVERT(DATE_SUB(go.created_at, INTERVAL 7 HOUR), date) as date,
go.market as market,
go.make,
go.model,
go.no_way,
go.repair_location,
go.source as website_source,
go.utm_content,
go.utm_source,
go.post_tax_amount_requested
FROM god_objects go
WHERE (go.is_a_test = 0 OR go.is_a_test IS NULL)
AND (go.carparts = 0 OR go.carparts IS NULL)
AND (go.no_way = 0 OR go.no_way IS NULL)
), tableau_sales as (
SELECT
q.id as quote_id,
go.id as lead_id,
j.id as job_id,
go.market,
CONVERT(DATE_SUB(go.sold_at, INTERVAL 7 HOUR), date) as date,
CONVERT(DATE_SUB(go.initial_job_date, INTERVAL 7 HOUR), date) as initial_job_date,
go.post_tax_amount_requested,
go.amount_collected,
go.customer_id,
go.make,
go.model,
go.repair_location,
go.source as website_source,
go.utm_content,
go.utm_source,
q.balance_amount_due,
q.assigned_technician_id,
q.payment_status,
q.quote_grand_total,
q.total_transaction_amount,
j.is_active,
j.technician_id
FROM quotes q
LEFT JOIN god_objects go ON go.id = q.lead_id
LEFT JOIN jobs j ON go.id = j.lead_id
WHERE (go.is_a_test = 0 OR go.is_a_test IS NULL)
AND (go.carparts = 0 OR go.carparts IS NULL)
AND go.initial_job_date IS NOT NULL
AND go.post_tax_amount_requested >= 200.0
) SELECT
tableau_leads.market,
tableau_leads.date,
sum(tableau_ads.clicks) as ad_clicks,
sum(tableau_ads.conversions) as ad_conversions,
sum(tableau_ads.cost) as ad_cost,
sum(tableau_ads.impressions) as ad_impressions,
COUNT(tableau_leads.lead_id) as lead_count,
COUNT(tableau_sales.quote_id) as sale_count,
SUM(tableau_leads.post_tax_amount_requested) as lead_amount_requested,
SUM(tableau_sales.post_tax_amount_requested) as sale_amount_requested
FROM tableau_leads
LEFT JOIN tableau_ads ON (tableau_leads.market = tableau_ads.market AND tableau_leads.date = tableau_ads.date)
LEFT JOIN tableau_sales ON (tableau_leads.market = tableau_sales.market AND tableau_leads.date = tableau_sales.date)
GROUP BY market, date
Again, any help would be greatly appreciated! 