I configured mysql using the JDBC plugin and the metadata is available in
INFORMATION_SCHEMA.
0: jdbc:drill:> select * from INFORMATION_SCHEMA.`TABLES` where TABLE_SCHEMA =
'mysql';
+----------------+---------------+----------------------------+-------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE |
+----------------+---------------+----------------------------+-------------+
| DRILL | mysql | BUCKETING_COLS | TABLE |
| DRILL | mysql | CDS | TABLE |
| DRILL | mysql | COLUMNS_V2 | TABLE |
| DRILL | mysql | DATABASE_PARAMS | TABLE |
| DRILL | mysql | DBS | TABLE |
| DRILL | mysql | DB_PRIVS | TABLE |
| DRILL | mysql | FUNCS | TABLE |
| DRILL | mysql | FUNC_RU | TABLE |
| DRILL | mysql | GLOBAL_PRIVS | TABLE |
| DRILL | mysql | IDXS | TABLE |
| DRILL | mysql | INDEX_PARAMS | TABLE |
| DRILL | mysql | PARTITIONS | TABLE |
| DRILL | mysql | PARTITION_KEYS | TABLE |
| DRILL | mysql | PARTITION_KEY_VALS | TABLE |
| DRILL | mysql | PARTITION_PARAMS | TABLE |
| DRILL | mysql | PART_COL_PRIVS | TABLE |
| DRILL | mysql | PART_COL_STATS | TABLE |
| DRILL | mysql | PART_PRIVS | TABLE |
| DRILL | mysql | ROLES | TABLE |
| DRILL | mysql | SDS | TABLE |
| DRILL | mysql | SD_PARAMS | TABLE |
| DRILL | mysql | SEQUENCE_TABLE | TABLE |
| DRILL | mysql | SERDES | TABLE |
| DRILL | mysql | SERDE_PARAMS | TABLE |
| DRILL | mysql | SKEWED_COL_NAMES | TABLE |
| DRILL | mysql | SKEWED_COL_VALUE_LOC_MAP | TABLE |
| DRILL | mysql | SKEWED_STRING_LIST | TABLE |
| DRILL | mysql | SKEWED_STRING_LIST_VALUES | TABLE |
| DRILL | mysql | SKEWED_VALUES | TABLE |
| DRILL | mysql | SORT_COLS | TABLE |
| DRILL | mysql | TABLE_PARAMS | TABLE |
| DRILL | mysql | TAB_COL_STATS | TABLE |
| DRILL | mysql | TBLS | TABLE |
| DRILL | mysql | TBL_COL_PRIVS | TABLE |
| DRILL | mysql | TBL_PRIVS | TABLE |
| DRILL | mysql | VERSION | TABLE |
+----------------+---------------+----------------------------+-------------+
36 rows selected (0.128 seconds)
0: jdbc:drill:> select * from INFORMATION_SCHEMA.`COLUMNS` where TABLE_SCHEMA =
'mysql';
+----------------+---------------+---------------------+-------------------------+-------------------+-----------------+--------------+--------------------+---------------------------+-------------------------+--------------------+--------------------------+----------------+---------------------+----------------+---------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME
| ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE |
CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION |
NUMERIC_PRECISION_RADIX | NUMERIC_SCALE | DATETIME_PRECISION | INTERVAL_TYPE
| INTERVAL_PRECISION |
+----------------+---------------+---------------------+-------------------------+-------------------+-----------------+--------------+--------------------+---------------------------+-------------------------+--------------------+--------------------------+----------------+---------------------+----------------+---------------------+
| DRILL | mysql | BUCKETING_COLS | SD_ID
| 1 | null | NO | BIGINT |
null | null | 64 | 2
| 0 | null | null |
null |
| DRILL | mysql | BUCKETING_COLS | BUCKET_COL_NAME
| 2 | null | YES | CHARACTER VARYING |
255 | 1020 | null | null
| null | null | null |
null |
| DRILL | mysql | BUCKETING_COLS | INTEGER_IDX
| 3 | null | NO | INTEGER |
null | null | 32 | 2
| 0 | null | null |
null |
| DRILL | mysql | CDS | CD_ID
| 1 | null | NO | BIGINT
.........
| CHARACTER VARYING | 127 | 508 |
null | null | null | null
| null | null |
| DRILL | mysql | VERSION | VERSION_COMMENT | 3
| null | NO | CHARACTER VARYING | 255
| 1020 | null | null | null
| null | null | null |
+---------------+--------------+------------+------------------+------------------+----------------+-------------+--------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+
211 rows selected (0.179 seconds)
But pending on the JDBC source it may require some finesse. I.e. in this case
mysql performance_schema has issues.
0: jdbc:drill:> select * from INFORMATION_SCHEMA.`COLUMNS`;
Error: SYSTEM ERROR: MySQLSyntaxErrorException: Unknown table engine
'PERFORMANCE_SCHEMA'
Fragment 0:0
[Error Id: 39952a3f-3f76-4b62-97b4-4321df0804f9 on drilldemo:31010]
(state=,code=0)
--Andries
> On Aug 11, 2016, at 6:15 AM, Magnus Pierre <[email protected]> wrote:
>
> Hello Rahul,
>
> show tables in <storage_plugin>.<schema>, and describe should possibly
> provide what you would like to see.
> It works on my DB2 at least so please try and see if it works for you as well.
>
> Regards,
> Magnus
>
>
>
>> 11 aug 2016 kl. 15:09 skrev Rahul Raj <[email protected]>:
>>
>> Hi,
>>
>> Does drill allow querying JDBC metadata from a storage plugin? I am trying
>> to get the list of tables/views and their respective columns.
>>
>> If not, is it possible to develop rest apis within drill to achive this?
>>
>> Regards,
>> Rahul
>>
>> --
>> **** This email and any files transmitted with it are confidential and
>> intended solely for the use of the individual or entity to whom it is
>> addressed. If you are not the named addressee then you should not
>> disseminate, distribute or copy this e-mail. Please notify the sender
>> immediately and delete this e-mail from your system.****
>
