title: Reading Hive view created with CTE (With clause) from spark
body: <p>I have a view on Hive created with CTE (WITH clause) that, union two tables, then compute to show only the most recent record per id.
  In my env, I have a tool for browsing hive databases (DBeaver, mandatory for non datalake dev to browse the data).</p>
  <p><strong>View code</strong></p>
  <pre class="lang-sql prettyprint-override"><code>    CREATE VIEW IF NOT EXISTS db.test_cte_view AS
        with cte as (select * from db.test_cte union select * from db.test_cte_2),
         tmp as (SELECT id, idate, ROW_NUMBER() over(PARTITION BY id ORDER BY idate desc ) AS row_num from cte)
        SELECT cte.* from cte
           join (SELECT * from tmp where tmp.row_num =1) tmp_2
           on cte.id = tmp_2.id
           and cte.idate = tmp_2.idate
  </code></pre>
  <p>The issue is:</p>
  <h2>when the view is created via beeline</h2>
  <p>(Which is our main way of creating tables and views in Hive)<br />
  I can easily browse on DBeaver, but, when running spark process to read from it it fails with the following:</p>
  <pre class="lang-py prettyprint-override"><code>    ##pyspark
      spark.sql(&quot;select * from db.test_cte_view&quot;).show()
  
      'Table or view not found: cte; line 3 pos 56'
      Traceback (most recent call last):
      File &quot;DATA/fs3/hadoop/yarn/local/usercache/ingouagn/appcache/application_1552132357519_15102/container_e378_1552132357519_15102_01_000001/pyspark.zip/pyspark/sql/session.py&quot;, line 545, in sql
        return DataFrame(self._jsparkSession.sql(sqlQuery), self._wrapped)
      File &quot;/DATA/fs3/hadoop/yarn/local/usercache/ingouagn/appcache/application_1552132357519_15102/container_e378_1552132357519_15102_01_000001/py4j-0.10.4-src.zip/py4j/java_gateway.py&quot;, line 1133, in __call__
        answer, self.gateway_client, self.target_id, self.name)
      File &quot;/DATA/fs3/hadoop/yarn/local/usercache/ingouagn/appcache/application_1552132357519_15102/container_e378_1552132357519_15102_01_000001/pyspark.zip/pyspark/sql/utils.py&quot;, line 69, in deco
        raise AnalysisException(s.split(': ', 1)[1], stackTrace)
      pyspark.sql.utils.AnalysisException: 'Table or view not found: cte; line 3 pos 56'
  </code></pre>
    
  <h2>When the view is created with sparl.sql(&quot;CREATE VIEW AS ...&quot;)</h2>
  <p>I can read it well with</p>
  <pre class="lang-py prettyprint-override"><code>    ##pyspark
      spark.sql(&quot;select * from db.test_cte_view&quot;).show()
  </code></pre>
  <p>But, when trying to browse with DBeaver, it fails with something like:</p>
  <pre><code>Query execution failed
  
  Reason:
  SQL Error [40000] [42000]: Error while compiling statement: FAILED: SemanticException line 1:330 Failed to recognize predicate 'UNION'. Failed rule: 'identifier' in subquery source in definition of VIEW test_cte_view [
  SELECT `gen_attr_0` AS `id`, `gen_attr_1` AS `status`, `gen_attr_2` AS `idate` FROM (SELECT `gen_attr_0`, `gen_attr_1`, `gen_attr_2` FROM ((SELECT `gen_attr_0`, `gen_attr_1`, `gen_attr_2` FROM (SELECT `id` AS `gen_attr_0`, `status` AS `gen_attr_1`, `idate` AS `gen_attr_2` FROM `db`.`test_cte`) AS gen_subquery_0) UNION DISTINCT (SELECT `gen_attr_5`, `gen_attr_6`, `gen_attr_7` FROM (SELECT `id` AS `gen_attr_5`, `status` AS `gen_attr_6`, `idate` AS `gen_attr_7` FROM `db`.`test_cte_2`) AS gen_subquery_1)) AS cte INNER JOIN (SELECT `gen_attr_3`, `gen_attr_4`, `gen_attr_8` FROM (SELECT `gen_attr_3`, `gen_attr_4`, `gen_attr_8` FROM (SELECT gen_subquery_4.`gen_attr_3`, gen_subquery_4.`gen_attr_4`, row_number() OVER (PARTITION BY `gen_attr_3` ORDER BY `gen_attr_4` DESC NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `gen_attr_8` FROM (SELECT `gen_attr_3`, `gen_attr_4` FROM ((SELECT `gen_attr_3`, `gen_attr_9`, `gen_attr_4` FROM (SELECT `id` AS `gen_attr_3`, `status` AS `gen_attr_9`, `idate` AS `gen_attr_4` FROM `db`.`test_cte`) AS gen_subquery_2) UNION DISTINCT (SELECT `gen_attr_5`, `gen_attr_6`, `gen_attr_7` FROM (SELECT `id` AS `gen_attr_5`, `status` AS `gen_attr_6`, `idate` AS `gen_attr_7` FROM `db`.`test_cte_2`) AS gen_subquery_3)) AS cte) AS gen_subquery_4) AS gen_subquery_5) AS tmp WHERE (`gen_attr_8` = 1)) AS tmp_2 ON ((`gen_attr_0` = `gen_attr_3`) AND (`gen_attr_2` = `gen_attr_4`))) AS cte
  ] used as test_cte_view at Line 1:14
  </code></pre>
  <p>It seems the generated code is different between one way of creating the view to another.</p>
  <p>Is there a way to make the first scenario (creating the view via beeline and accessing it via spark sql) work?</p>
  <p>Thank you.</p>
  <p><strong>spark: 2.1.1</strong>  , <strong>Hive: 1.2.1</strong></p>
  <h2>Tables</h2>
  <pre class="lang-sql prettyprint-override"><code>     CREATE TABLE db.test_cte(
        id int, 
        status string, 
        idate date )
    
       CREATE TABLE db.test_cte_2(
        id int, 
        status string, 
        idate date )
  </code></pre>
  <p>populated with:</p>
  <pre class="lang-sql prettyprint-override"><code>      insert into db.test_cte values
        (1,&quot;green&quot;,&quot;2019-03-08&quot;),
        (2,&quot;green&quot;,&quot;2019-03-08&quot;),
        (3,&quot;green&quot;,&quot;2019-03-08&quot;),
        (1,&quot;red&quot;,&quot;2019-03-09&quot;),
        (1,&quot;yellow&quot;,&quot;2019-03-10&quot;),
        (2,&quot;gray&quot;,&quot;2019-03-09&quot;)
    
        insert into db.test_cte_2 values
        (10,&quot;green&quot;,&quot;2019-03-08&quot;),
        (20,&quot;green&quot;,&quot;2019-03-08&quot;),
        (30,&quot;green&quot;,&quot;2019-03-08&quot;),
        (10,&quot;red&quot;,&quot;2019-03-09&quot;),
        (10,&quot;yellow&quot;,&quot;2019-03-10&quot;),
        (20,&quot;gray&quot;,&quot;2019-03-09&quot;)
  </code></pre>
  <p><strong>Edit</strong>:<br />
  For anyone interested, I created an issue on Spark JIRA:<br />
  <a href="https://issues.apache.org/jira/browse/SPARK-27203" rel="nofollow noreferrer">https://issues.apache.org/jira/browse/SPARK-27203</a></p>