Hive创建含常量字符串列的视图出现异常问题

问题重现 #

执行以下语句创建视图：

CREATE VIEW IF NOT EXISTS v_all_products AS (
    SELECT 
        *,
        'credit' AS product  
    FROM 
        credit
    UNION
    SELECT
        *,
        'deposit' AS product  
    FROM  
        deposit
);

但在DBeaver中创建完成后，视图定义变成：

CREATE OR REPLACE VIEW `v_all_products` AS (;);

解决方法 #

• 移除外层多余括号：Hive创建视图时，AS后的子查询无需额外包裹外层括号，多余括号可能导致解析异常。修改后的语句：

  CREATE VIEW IF NOT EXISTS v_all_products AS
      SELECT 
          *,
          'credit' AS product  
      FROM 
          credit
      UNION
      SELECT
          *,
          'deposit' AS product  
      FROM  
          deposit;
  

• 显式指定列名：避免使用*带来的列匹配歧义，尤其是当两张表列结构不完全一致时，显式列出所有列再加常量列更稳妥。假设两张表都有id、name列，语句示例：

  CREATE VIEW IF NOT EXISTS v_all_products AS
      SELECT 
          id,
          name,
          'credit' AS product  
      FROM 
          credit
      UNION
      SELECT
          id,
          name,
          'deposit' AS product  
      FROM  
          deposit;
  

• 检查DBeaver驱动兼容性：确保DBeaver使用的Hive JDBC驱动版本与集群Hive版本匹配，旧驱动可能存在解析Bug。可以尝试更新驱动，或者直接用Hive CLI/Beeline执行语句，验证是否是DBeaver的问题。

• 换用CREATE OR REPLACE VIEW：部分Hive版本对IF NOT EXISTS的处理存在异常，换成CREATE OR REPLACE VIEW尝试：

  CREATE OR REPLACE VIEW v_all_products AS
      SELECT 
          *,
          'credit' AS product  
      FROM 
          credit
      UNION
      SELECT
          *,
          'deposit' AS product  
      FROM  
          deposit;
  

内容的提问来源于stack exchange，提问作者Alexander Lopatin