Metadata queries

#1

Hi,

my SQL queries on metadata tables do not return anything.

eg select * from INFORMATION_SCHEMA.VIEWS
or select * from INFORMATION_SCHEMA.TABLES
return empty datasets

It is the case in 4.0.2 and in 4.0.5.
Am I alone in this case ?

Thanks

#2

@dfleckinger

Works as expected. Would you be able to run the query and send us the profile? Also can you check if the server.log has any exceptions?

Thanks
@balaji.ramaswamy

#3

The query is as above ,

select * from INFORMATION_SCHEMA.VIEWS

here is the profile
24ed062c-7080-4fe6-85d0-e659119f3d82.zip (6.2 KB)

and no exception in server.log

2019-11-26 13:48:00,269 [out-of-band-observer] INFO query.logger - {“queryId”:“2222d2f0-3e76-65b6-5cb3-0f4480c22a00”,“schema”:"[@david]",“queryText”:“select * from INFORMATION_SCHEMA.VIEWS”,“start”:1574776079913,“finish”:1574776080268,“outcome”:“COMPLETED”,“username”:“david”}

Thanks

#4

Hello
We experience similar issues and expect a fix. In fact it works if the query doesn’t return a large number of records …note : We are running Enterprise edition. Case is open for quite a long time and wait patiently to be fixed.

#5

Thanks ! So it seems to happen only when there is a large number of VDS , etc… ?
I tried to reduce the number of results by filtering eg
SELECT * FROM INFORMATION_SCHEMA.“TABLES”
where TABLE_NAME like ‘C%’ or using the LIMIT clause.

but still I get no results. Did you find any trick ?

#6

Do you get this error in the Profile -> Error? I managed to dig up the error below after some digging through profiles and Yarn logs:

dataset listing failed: java.util.concurrent.TimeoutException

For us the root cause was a hard coded RPC timeout, which comes into play when your executor is not on the same server as your coordinator (in our case a Yarn executor cluster). When this happens the dataset listing query executes over RPC on the coordinator; vs when the executor and coordinator are the same box and it executes locally (which has no timeout).

com.dremio.service.listing.DatasetListingInvoker.

final ProtocolBuilder builder = ProtocolBuilder.builder()
    .protocolId(57)
    .allocator(allocator)
    .name("dataset-listing-rpc")
    .timeout(10 * 1000);

The above code sets a hard coded timeout of 10 seconds. We found that with large numbers of VDS, some queries take 4 minutes to complete over RPC, though only a few seconds when the coordinator and executor are the same box. We are working to expose this timeout as a support key in our local code base.

#7

@dfleckinger

I see there are no files that Dremio is reading. Does INFORMATION_SCHEMA.“TABLES” return results?

Thanks
@balaji.ramaswamy

#8

@balaji.ramaswamy could you explain what you mean by return results?

Both queries
select * from INFORMATION_SCHEMA.“VIEWS”
and
select * from INFORMATION_SCHEMA.“TABLES”

return no results, with no error, and immediately

#9

@dfleckinger

I assume you have datasets in these sources? Try this. Shutdown the executor and the coordinator then “cd DREMIO_HOME/bin” and run “./dremio-admin clean” and send us the output

Restart coordinator and then the executors

thanks
@balaji.ramaswamy

#10

Of course I have an important number of datasets in these sources (several hundreds)

thanks, here the output of the command dremio-admin clean
dremio_clean_output.txt.zip (999 Bytes)

#11

Hi @balaji.ramaswamy , are there any news on this topic ?

#12

@dfleckinger

Let’s do this,

Do a run (not preview) of the below SQL and send me the profiles

select * from information_schema.“tables”
select * from information_schema.views

Share a query profile

#13

here they are
select * from information_schema.“tables”
tables_a736c296-3c5e-4613-bd4c-7a30960deb88.zip (6.3 KB)

select * from information_schema.views
views_49d68adf-3f37-4f6e-afee-cc53f1ca88ee.zip (6.3 KB)

#14

@dfleckinger

Other than this issue ,are you able to see all your datasets on the UI and do they work as expected?

Thanks
@balaji.ramaswamy

#15

Well, it’s difficult to say, as I have hundreds of datasets, so I can not test all of them in the UI.
but in my day to day activity in the UI, I do not have issues in the UI, they work as expected.