Skip to main content

how to get hive table size from metastore mysql

select    d.name  as db_name
,t.tbl_name     as tbl_name
,from_unixtime(min(t.create_time))   as create_time
,min(t.owner)          as owner
,min(case when tp.param_key = 'COLUMN_STATS_ACCURATE'  then tp.param_value                 end) as COLUMN_STATS_ACCURATE
,min(case when tp.param_key = 'last_modified_by'       then tp.param_value                 end) as last_modified_by
,min(case when tp.param_key = 'last_modified_time'     then from_unixtime(tp.param_value)  end) as last_modified_time 
,min(case when tp.param_key = 'numFiles'               then tp.param_value                 end) as numFiles
,min(case when tp.param_key = 'numRows'                then tp.param_value                 end) as numRows
,min(case when tp.param_key = 'rawDataSize'            then tp.param_value                 end) as rawDataSize
,min(case when tp.param_key = 'totalSize'              then tp.param_value                 end) as totalSize
,min(case when tp.param_key = 'transient_lastDdlTime'  then from_unixtime(tp.param_value)  end) as transient_lastDdlTime

from DBS as d
join TBLS as t
on t.db_id = d.db_id
join TABLE_PARAMS  as tp
on tp.tbl_id = t.tbl_id
where d.name='tmp_work_db'
group by  d.name ,t.tbl_name
order by  d.name,t.tbl_name 

Comments

  1. Alfred AvinaJanuary 24, 2020 at 2:31 AM

    As the growth of Big data modernization solutions , it is essential to spread knowledge in people. This meetup will work as a burst of awareness.

    ReplyDelete

Post a Comment

Popular posts from this blog

Hadoop Yarn MR(MapReduce) streaming using Shell script part 2

Friends, This is a streaming MapReduce job (shell script) that reads any text input and computes the average length of all words that start with each character . --------------------------------------------------------------------------------------------------------------------------------------------------------------- $ cat avg_ln_mpr.sh #! /bin/bash while  read  line do  for word in `echo $line`  do     c=`expr substr $word 1 1`     l=`expr length $word`     echo $c $l  done     done --------------------------------------------------------------------------------------------------------------------------------------------------------------- $ cat avg_ln_rdr.sh #! /bin/bash old='' new='' val='' cnt=1 sum=0 avg=0 start=0 while  read  line do new=`echo $line|cut -d' ' -f1` val=`echo $line|cut -d' ' -f2` if [ "$old" != "$new" ]; then [ $start -ne 0 ] &...
Post a Comment
Read more

MySQL replication - Master Slave Easy way with Crash test sample

I expect we have Master and Slave machines having MySQL installed  on both with server-id as 1 and 2 on Master and Slave . Mysql Replication steps: On Master: stop all transactions. mysql> FLUSH TABLES WITH READ LOCK; mysql> show master status ; +---------------+----------+--------------+------------------+ | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------+----------+--------------+------------------+ | binlog.000005 |  4913710 |              |                  | +---------------+----------+--------------+------------------+ take mysql dump of Master $ mysqldump -u root -p --all-databases --master-data > dbdump.sql mysql> unlock tables; transfer dump file  to slave host scp dbdump.sql  usr@slave:/tmp/ On Slave: [usr@slave ~]$ ls -ltr -rwx------ 1 usr usr 57319214 Nov  6 06:06 dbdump.sql...
Post a Comment
Read more