The connection and difference between database and data warehouse

Background

In the two years since I have been exposed to big data, the word data warehouse has frequently appeared in my ears. Here, with the gradual accumulation of knowledge, I also have a general understanding of data warehouses and databases.

Concept

Database (DB)

Simply put, a database is a warehouse that stores data, mainly relational Database and non-relational database, this is a logical concept.

Data warehouse (DW)

Logically understood, it is consistent with the concept of database. They are both warehouses for storing data, but the data volume of data warehouse is larger.

Characteristics of data warehouse

• Data warehouse is subject-oriented

So what is a topic Well, to put it simply, the topic is the aspect that users are concerned about when using the data warehouse.

• The data warehouse does not support modification. This is easy to understand. Unlike the database, the data warehouse does not support update and delete operations.

• The data in the data warehouse changes with time. This does not conflict with the previous article. This change does not refer to changes caused by update or delete, but changes with the change of time. As time changes, new content is constantly added or old content is deleted.

• The data warehouse is an integration of multiple heterogeneous data sources

The data warehouse generally stores historical data

• The data warehouse is weakly transactional, because the data warehouse stores historical data and generally reads (analyzes) data scenarios.

• Databases are designed to capture data, and data warehouses are designed to analyze data.

The difference between database and data warehouse

Database is designed to capture data, while data warehouse is designed to analyze data. The difference between database and data warehouse is essentially the difference between OLTP and OLAP

OLTP: On-Line Transaction Processing is a traditional relational database application.

OLTP is user-oriented and used for programmer transaction processing and customer query processing.

OLTP systems emphasize database memory efficiency, the command rate of various memory indicators, binding variables, and concurrent operations. Users are more concerned about issues such as operation response time, data security, integrity, and the number of concurrent supported users.

Since access to the OLTP system must ensure atomicity, there is a transaction mechanism and recovery mechanism. Mainly used for data scraping.

OLAP: On-Line Analytical Processing

OLAP is market-oriented and used for data analysis by knowledge workers (managers, supervisors and data analysts).

OLAP usually integrates data from multiple heterogeneous data sources in huge amounts.

is the main application of the data warehouse system, supporting complex analysis operations, focusing on decision support, and providing intuitive and easy-to-understand query results.

OLAP systems emphasize data analysis, SQL execution market, disk I/O, partitioning, etc. Mainly used for data analysis.

OLAP systems generally store historical data, so most of them are read-only operations and do not require transactions.

The above is the detailed content of The connection and difference between database and data warehouse. For more information, please follow other related articles on the PHP Chinese website!