0

I am trying to build a query to fetch columns from the system.CATALOG table and to continue querying based on the resultset. I looked at a few queries but seem to be unable to find anything that satisfies my requirements. I don't have much to show, that I have tried as I don't know, how to approach this.

I am using Apache Phoenix DB. (Any SQL is also OK, as I am interested in learning.)

I have now written the query below, which will fetch me all the column names, that start with A in schema test for table element. 

SELECT
  COLUMN_NAME
FROM SYSTEM.CATALOG
WHERE TABLE_SCHEM = 'TEST'
  AND TABLE_NAME = 'ELEMENT'
  AND COLUMN_NAME LIKE 'A%'";

Now I want to use the list of columns names in an UPSERT query from the resultset of above query, to update these columns in the element table's records. So I am stuck here.

Halfwarr
  • 7,853
  • 6
  • 33
  • 51
Satya
  • 153
  • 5
  • 15
  • 1
    Not sure, what you are actually looking for. Using [sqlline](http://sqlline.sourceforge.net/#sect_command_describe), you could `!describe [table name]`. Otherwise, following [Phoenix Grammar](https://phoenix.apache.org/language/index.html), you should be able to issue something like `SELECT * FROM system.CATALOG LIMIT 1` to see all columns. From either output, you should be able to construct your query. Or are you having trouble to continue from something like `select table_name, column_name, table_type from system.catalog where table_name='t1';`? – Abecee Aug 08 '15 at 09:53
  • Perhaps you could use [SQL Fiddle](http://www.sqlfiddle.com/) to re-create/demonstrate your problem. – Abecee Aug 08 '15 at 09:53
  • @Abecee I have the query to pick up the columns from system catalog table, however I want to use these columns in another to update a row or rows using upsert select query in phoenix. I know I should use sub query but I don't know how to make resultset of sub query as columns to main query – Satya Aug 09 '15 at 01:09
  • Your problem description as it stands, is too vague to address it properly and efficiently. Please add your current query, its output, and what the statement, you try to come up with, should actually look like / deliver. Then let's see, whether SQL provides the means for that. – Abecee Aug 11 '15 at 08:30
  • @ Abecee I added additional information. I have also written below query `UPSERT INTO TEST.ELEMENT(el_id, a.*) VALUES SELECT EL_ID, (SELECT COLUMN_NAME FROM SYSTEM.CATALOG WHERE TABLE_SCHEM = 'TEST' AND TABLE_NAME = 'ELEMENT' AND COLUMN_NAME LIKE 'A%) A FROM TEST.ELEMENT WHERE EL_PARENT_ID IN (10000)` – Satya Aug 11 '15 at 15:11
  • Does the query in your comment run successfully in Apache Phoenix DB? Does it get anywhere close to what you intend to do? Usually, you'd have either **VALUES** OR a query - but not both in the same statement. Similarly the **a.\*** in the list of columns to be written looks unusual. From your original description, I'd guess, you want **a.\*** to be replaced by all the columns starting with **A**. And it looks, as if you want to insert more records into the same table **element**. To cut it short: Retrieving column info from meta data tables, and then to insert data into the identified columns – Abecee Aug 11 '15 at 19:03
  • , does usually require dynamic SQL - as this means to change base half way through. In other words: I'd suggest to retrieve the **A** column names, as you did so far, and then to use that list in a hand written statement. Perhaps, you anticipate changes in the table structure, and want to be prepared for these. But -if at all- this would be a later step, once the basic statement structure has been sorted out. For the moment, it would help to get some sample data from the **element** table, and a specification, what should happen based on which criterion along `EL_PARENT_ID IN (10000)`. – Abecee Aug 11 '15 at 19:03

2 Answers2

0

try this, it works perfect.

SELECT column_name
  FROM system.catalog
  WHERE table_name = 'your_table' AND key_seq IS NOT NULL
Evhz
  • 8,852
  • 9
  • 51
  • 69
0

Example: To get the salt buckets on the table :

select table_name, salt_buckets 
from SYSTEM.CATALOG 
where salt_buckets is not null and table_name='TABLE_NAME';
Shiva Garg
  • 826
  • 9
  • 17