How can check Hive table size?

HOW TO: Find Total Size of Hive Database/Tables in BDM?

1. SELECT SUM(PARAM_VALUE) FROM TABLE_PARAMS WHERE PARAM_KEY=”totalSize”;
2. Get the table ID of the Hive table forms the TBLS table and run the following query:
3. SELECT TBL_ID FROM TBLS WHERE TBL_NAME=’test’;
4. SELECT * FROM TABLE_PARAMS WHERE TBL_ID=5109;

How do you enable compression on a hive table?

Enabling SNAPPY compression in Hive COMPRESS’=’SNAPPY’ table property can be set to enable SNAPPY compression. You can alternatively set parquet. compression=SNAPPY in the “Custom hive-site settings” section in Ambari for either IOP or HDP which will ensure that Hive always compresses any Parquet file it produces.

What happens when a managed table is dropped in hive?

Managed Table/Internal Table. In Hive,” user/hive/warehouse” is the default directory. We do not have to provide the location manually while creating the table. “Drop table” command deletes the data permanently.

Where does the data of a hive table gets stored?

The data loaded in the hive database is stored at the HDFS path – /user/hive/warehouse. If the location is not specified, by default all metadata gets stored in this path.

How can I check Hdfs table size?

You can use the hdfs dfs -du /path/to/table command or hdfs dfs -count -q -v -h /path/to/table to get the size of an HDFS path (or table).

How do I see all tables in Hive?

Switch to the Hive schema and issue the SHOW TABLES command to see the Hive tables that exist. Switch to the HBase schema and issue the SHOW TABLES command to see the HBase tables that exist within the schema.

What are the compression techniques in Hive?

The four most widely used Compression formats in Hadoop are as follows:

• GZIP. Provides High compression ratio. Uses high CPU resources to compress and decompress data.
• BZIP2. Provides High compression ratio (even higher than GZIP).
• LZO. Provides Low compression ratio.
• SNAPPY.

How does Snappy compression work?

The principle being that file sizes will be larger when compared with gzip or bzip2. Google says; Snappy is intended to be fast. On a single core of a Core i7 processor in 64-bit mode, it compresses at about 250 MB/sec or more and decompresses at about 500 MB/sec or more.

What happened when a managed table is dropped?

If a managed table or partition is dropped, the data and metadata associated with that table or partition are deleted. If the PURGE option is not specified, the data is moved to a trash folder for a defined duration.

When an external table is dropped in Hive?

When you run DROP TABLE on an external table, by default Hive drops only the metadata (schema). If you want the DROP TABLE command to also remove the actual data in the external table, as DROP TABLE does on a managed table, you need to configure the table properties accordingly.

What is Metastore?

Metastore is the central repository of Apache Hive metadata. It stores metadata for Hive tables (like their schema and location) and partitions in a relational database. It provides client access to this information by using metastore service API. A service that provides metastore access to other Apache Hive services.

How do you know if a hive table is internal or external?

For external tables Hive assumes that it does not manage the data. Managed or external tables can be identified using the DESCRIBE FORMATTED table_name command, which will display either MANAGED_TABLE or EXTERNAL_TABLE depending on table type.

How to compress data in a hive table?

So far we have been inserting data into the table by setting the following properties hive> set hive.exec.compress.output=true; hive> set avro.output.codec=snappy; However, if someone forgets to set the above two properties the compression is not achieved.

Why doesn’t hive keep stats on the external table?

Since this is an external table ( EXTERNAL_TABLE), Hive will not keep any stats on the table since it is assumed that another application is changing the underlying data at will. Why keep stats if we can’t trust that the data will be the same in another 5 minutes?

How do I tell hive about file formats in HDFS?

If you create a Hive table over an existing data set in HDFS, you need to tell Hive about the format of the files as they are on the filesystem (“schema on read”). For text-based files, use the keywords STORED as TEXTFILE.

Is there a way to enforce compression on table itself?

However, if someone forgets to set the above two properties the compression is not achieved. I was wondering if there is a way to enforce compression on table itself so that even if the above two properties are not set the data is always compressed? Yes, you can set the properties in the table.