Shulou(Shulou.com)05/31 Report--

This article mainly introduces the differences between the database and the data warehouse, the article is very detailed, has a certain reference value, interested friends must read it!

The database is operational, and the data warehouse is analytical.

1. Operational database

Mainly used for business support. A company often uses and maintains several databases that store the company's daily operation data, such as merchandise purchase, hotel reservation, student score entry, etc.

two。 Analytical database

It is mainly used for historical data analysis. As the separate data storage of the company, this kind of database is responsible for the statistical analysis of each subject domain of the company by using historical data.

1. Data composition difference-data time range difference

Generally speaking, operational databases only store data within 90 days, while analytical databases store data within several years. This is also the main reason for the physical separation of operational and analytical data.

two。 Differences in data composition-differences in data levels of detail

Operational database mainly stores detailed data, while analytical database has both detailed data and summary data, but for users, it focuses on the part of summary data.

Naturally, there are summary requirements in operational databases, but the summary data itself is not stored but only its generation formula. This is because operational data is dynamic, so summary data is generated dynamically at each query.

For the analytical database, because the summary data is relatively stable and will not change, and its amount of calculation is relatively large (because of the large time span), so its summary data can be calculated in advance to avoid double calculation.

3. Data composition difference-data time representation difference

Operational data usually reflects the current state of the real world, while analytical databases have both current states and snapshots of past moments, and users of analytical databases can synthesize all snapshots for statistical analysis of each historical stage.

4. Technical differences-differences in the total amount of query data and query frequency

Operational query has a small amount of data and more frequency, while analytical query has a large amount of data and less frequency. It is impossible to achieve configuration optimization in both cases, which is one of the reasons why the two types of databases are physically separated.

5. Technical differences-data update differences

Operational database allows users to add, delete, change, and check, while analytical database users can only query.

6. Technical differences-data redundancy differences

What is the meaning of the data? Is to reduce data redundancy and avoid update exceptions. As mentioned in 5, there is no update operation in the analytical database. Therefore, it is less important to reduce data redundancy.

Let's go back to the second question mentioned at the beginning. "is it normal that the relational tables in the Hadoop Hive of a large company do not fully meet the complete / referential constraints, the requirements of the paradigm, or even the first paradigm?" It is normal to answer. Because Hive is a kind of data warehouse, and the relationship between data warehouse and analytical database is very close (which will be discussed later). It only provides query interface, not update interface, which makes many measures to eliminate redundancy do not need to be strictly implemented.

7. Functional differences-data reader differences

The users of the operational database are the various roles in the business environment, such as users, merchants, importers, etc., while the analytical database is only used by a small number of users to make comprehensive decisions.

8. Functional differences-data location differences

The positioning here mainly refers to the purpose of organizing. Operational database is to support specific business, so it is also called "application-oriented database"; analytical database is created for the analysis tasks of specific business topic domains, so it is also called "topic-oriented database".

These are all the contents of the article "what is the difference between database and data warehouse". Thank you for reading! Hope to share the content to help you, more related 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.