2

For retrieving data from Hive SQL, what all clauses a query could start with ? I only know of SELECT and WITH. Is there anything else ?

I'm not looking anything like CREATE, DELETE, ALTER etc..

This is basically for deciding whether a given query is for reading data from table or not (using query.startsWith()).

Thanks in advance !

gom
  • 23
  • 5

1 Answers1

0

SQL query can start with WITH, FROM, SELECT.

Multi-insert looks like this:

[WITH...]
[FROM...]
INSERT OVERWRITE TABLE1 SELECT...
INSERT OVERWRITE TABLE2 SELECT...

WITH can be omitted, it can start with FROM, it can be SELECT or INSERT...SELECT.

This query works. It starts with FROM and does not read data from table

from (select current_timestamp ts) s
select ts

The same inserting into multiple tables, of course the example is oversimplified but quite working

with mydata as (select current_timestamp ts)

from (select ts from mydata) s

insert into table1 
select ts

insert into table2
select ts
where ts = current_timestamp
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Thanks for quick response.! If I understand correctly, below does not not read data from table.. meaning it does not return any rows.. If so, what could be the use of this ? `from (select current_timestamp ts) s select ts` – gom Sep 01 '20 at 09:15
  • @gom It returns single row, single column. And there are practical use in queries which are not reading tables. If you need to generate some data on the fly. For example calendar data, see example https://stackoverflow.com/a/56071699/2700344 – leftjoin Sep 01 '20 at 10:22
  • @gom The problem is not only select can start with WITH, FROM. It can be INSERT also. Not sure if it is a problem for you or not – leftjoin Sep 01 '20 at 10:28
  • @gom And of course INSERT also can read data from some table. INSERT ... SELECT FROM... So queries which are reading data from tables can start with SELECT, WITH, FROM, INSERT. In ACID mode also MERGE and DELETE are available, these also can read data from tables in subqueries .... – leftjoin Sep 01 '20 at 10:35
  • Thanks for clarifications ! Actually, I'm not bothered about any query reading from table.. I'm more bothered about queries that return some rows / data. So far, looks like only SELECT, WITH and FROM can return any data. Again, Thanks a lot for your inputs.. much appreciated !! – gom Sep 02 '20 at 11:05