title: Large Queries Hanging with the MongoDB Bi Connector (and Tableau)
body: <p>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.</p>
  <p>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.</p>
  <p>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.</p>
  <p>Is there perhaps some kind of limitation that I am facing because I am not using MongoDB’s Atlas product?</p>
  <p>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.</p>
  <p>Below are two example queries that work fine:</p>
  <pre><code>SELECT 
    market,
    CONVERT(date, date) as date,
    clicks,
    conversions,
    cost,
    impressions
  FROM ad_metrics
  </code></pre>
  <pre><code>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)
  </code></pre>
  <p>and below is the query that hangs:</p>
  <pre><code>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 &gt;= 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
  </code></pre>
  <p>Is there something wrong with my SQL? Is there any other kind of issue? Again, any help would be greatly appreciated!</p>