System tables of OmnisciDB for tables list

Follow

Mohammad Sohail

• January 26, 2023 16:30

Hi, I am using DBeaver to connect to OmnisciDB, I don't see any system table where I can see the list of tables in a database. After research I found to query mapd_tables table but it says table not found.

Any suggestion for this is appreciated.

0

• 

  Candido Dessanti

  • January 27, 2023 08:21

  Hi Mohammad Sohail,

  Well probably we should enhance out integration with dbeaver (I did something in the past, but I never published it), but since the 5.10 (in beta) and the 6.0 release we have a read-only database called information_schema with system tables
  this is a list of 6.4 release:

  users
  databases
  permissions
  roles
  tables
  dashboards
  role_assignments
  memory_summary
  memory_details
  storage_details
  executor_pool_summary

  To use it from dbeaver you have to create a new connection using information_schema as a database, or if you are using the 6.4 you can query those table from any database using information_schema. as a prefix of the table name.

  You can query the tables as normal table e.g.

  select database_name,owner_user_name,table_name from tables where table_name like '%flight%' or table_name like '%heavy%' limit 10;
  database_name|owner_user_name|table_name
  heavyai|admin|heavyai_us_states
  heavyai|admin|flights_donotmodify
  heavyai|admin|flights_read
  heavyai|admin|flights_temp
  heavyai|admin|flights_2007
  heavyai|admin|flights_2007_2008
  heavyai|admin|flights_2006
  heavyai|admin|flights_2007_2008_2006
  heavyai|admin|flights_2008_7M
  heavyai|admin|flights_origin_dest_curves

  You can also use aggregates or joins.
  For example to know which are the 5 tables that are taking more space on your storage

  select database_name,table_name,sum((total_data_file_size+total_dictionary_data_file_size)/1024/1024) from storage_details group by 1,2 order by 3 desc limit 5;
  database_name|table_name|EXPR$2
  taxi|yellow_tripdata|30720
  tpch100|lineitem_2|29496
  tpch100|lineitem_ns|27168
  commuting|acq_geo|19024
  tpch40|lineitem|18856

  If you are on omnisci 5.9 or 5.10 make sure you tuyrn on the system tables using the parameter --enable-system-tables.

  For a more detailed description of system tables please refer to our docs.

  Best regards,

  Candido

   

  0

  Comment actions Permalink

Please sign in to leave a comment.