0

I have a sql like this:

SELECT s_store_name ,
   sum(ss_net_profit)
FROM store_sales ,
     date_dim ,
     store,
  (SELECT ca_zip
   FROM
 ( SELECT substr(ca_zip,1,5) ca_zip
  FROM customer_address
  WHERE substr(ca_zip,1,5) IN ( '27385',  '79307', '15492') INTERSECT
    SELECT ca_zip
    FROM
      (SELECT substr(ca_zip,1,5) ca_zip,
              count(*) cnt
       FROM customer_address,
            customer
       WHERE ca_address_sk = c_current_addr_sk
         AND c_preferred_cust_flag='Y'
       GROUP BY ca_zip
       HAVING count(*) > 10)A1)A2) V1
WHERE ss_store_sk = s_store_sk
  AND ss_sold_date_sk = d_date_sk
  AND d_qoy = 1
  AND d_year = 2002
  AND (substr(s_zip,1,2) = substr(V1.ca_zip,1,2))
GROUP BY s_store_name
ORDER BY s_store_name LIMIT 100;

now ,I want to convert it to hql,but hive don't support INTERSECT,what should I do? In addition hive seem to be dont't support as either.

Matrix42
  • 13
  • 2
  • Is executing this query as 'native query' an option? http://stackoverflow.com/questions/17708946/jpa-native-query-select-and-cast-object – actc Nov 30 '16 at 09:55
  • I save it as a file and `hive -f filename;` – Matrix42 Nov 30 '16 at 10:16
  • 2
    You can rewrite this sql to make it hive compatible. For INTERSECT, you can use an INNER JOIN. Also you would need to use a GROUP BY or DISTINCT if you have don't have a pure one-to-one relationship. – Reena Upadhyay Nov 30 '16 at 11:12
  • OK! Thanks a lot! – Matrix42 Nov 30 '16 at 13:13
  • @Matrix42 What version HIVE are you using? when you say 'as' are you reffering to an alias? select a.zip as ZIP, ? Aliasing works fine for me on Apache Hive, HIVE JDBC, Beeline (version 1.2.1.2.3.2.0-2950) – AM_Hawk Nov 30 '16 at 17:57
  • @AM_Hawk It‘s my fault ,hive support `as` – Matrix42 Dec 02 '16 at 07:26

0 Answers0