Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What are the common SQL view operations in Hive

2025-01-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Internet Technology >

Share

Shulou(Shulou.com)06/01 Report--

Editor to share with you what Hive commonly used SQL view operations are, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Brief introduction

The concept of the view in Hive is the same as that in RDBMS, which is the logical representation of a set of data and is essentially the result set of a SELECT statement. Views are purely logical objects with no associated storage (except for materialized views introduced by Hive 3.0.0). When a query references a view, Hive can combine the definition of the view with the query, such as pushing the filter in the query to the view.

View

Normal view: is a virtual table that does not store data itself, which is defined by query statements. To put it simply, the view simply defines the metadata of the data structure.

Materialized view: is a special physical table, the view itself holds data, its data source is based on the original table or remote table query, and the data will be updated regularly. Hive is currently only supported in version 3.0 and above.

Features of hive view

In Hive, you can use CREATE VIEW to create views. If a table or view with the same name already exists, an exception will be thrown. It is recommended to use IF NOT EXISTS to prejudge. You need to be aware of the following when using views:

Only logical views, no materialized views

View is read-only and cannot Load/Insert/Update/Delete data

The view of hive is only a shortcut to sql, in order to improve the readability of hql statements

When the view is created, only a copy of metadata is saved, and when the view is queried, the subqueries corresponding to the view are executed.

The view of hive is saved in the Metabase

The type saved in the Metabase is VIRTUAL_VIEW

The Metabase holds the sql statements represented by the current view

When you create a view, if the SELECT statement contains other expressions, such as x + y, the column name will be generated in the form of _ C0GradeC1, etc.

When you create a view, if no column name is provided, the column name is automatically derived from the SELECT statement

Deleting the base table does not delete the view, you need to delete the view manually

The view is fixed when the view is created, and subsequent changes to the base table (such as adding columns) will not be reflected in the view

The view may contain ORDER BY and LIMIT clauses. If the query statement that references the view also contains such clauses, its execution priority is lower than the corresponding clause of the view. For example, the view custom_view specifies LIMIT 5, the query statement is select * from custom_view LIMIT 10, and the result returns up to five rows.

Create a view create view view_name as select * from carss; create view carss_view as select * from carss limit 500; view view show tables; / / you can view tables or view desc view_name / / view information about a specific view desc carss_view delete view drop view view_namedrop view if exists carss_view

When deleting a view, if the deleted view is referenced by another view, the program will not issue a warning, but the other views referencing that view have become invalid and need to be rebuilt or deleted.

Use view create view sogou_view as select * from sogou_table where rank > 3; select count (distinct uid) from sogou_view; to modify view ALTER VIEW [db_name.] view_name AS select_statement

The changed view must exist, and the view cannot have partitions, and if the view has partitions, the modification fails.

The above is all the contents of the article "what are the common SQL view operations in Hive?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 278

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Internet Technology

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report