Читать книгу Official Google Cloud Certified Professional Data Engineer Study Guide - Dan Sullivan - Страница 48

Online analytical processing (OLAP) data models are often used for data warehouse and data mart applications. OLAP models are also called dimensional models because data is organized around several dimensions. OLAP models are designed to facilitate the following:

 Rolling up and aggregating data

 Drilling down from summary data to detailed data

 Pivoting and looking at data from different dimensions—sometimes called slicing and dicing

OLAP can be implemented in relational database or in specialized multidimensional data stores.

SQL Crash Course

In-depth knowledge of SQL is not necessarily required to pass the Google Cloud Professional Data Engineer exam, but knowledge of SQL may help if a question includes a SQL statement.

SQL has three types of statements that developers use:

 Data definition language (DDL) statements, which are used to create and modify database schemas

 Data manipulation language (DML) statements, which are used to insert, update, delete, and query data

 Data query language (DQL) statements, which is a single statement: SELECT

Table 1.4 shows examples of data definition statements and their function. Table 1.5 shows data manipulation examples, and Table 1.6 shows query language examples.

Table 1.4 Data definition language examples

DDL statement	Example	Explanation
CREATE TABLE	CREATE TABLE address (address_id INT PRIMARY KEY, street_name VARCHAR(50), city VARCHAR(50), state VARCHAR(2) );	Creates a table with four columns. The first is an integer and the primary key; the other three are variable-length character strings.
CREATE INDEX	CREATE INDEX addr_idx ON address(state);	Creates an index on the state column of the address table.
ALTER TABLE	ALTER TABLE address ADD (zip VARCHAR(9));	Adds a column called zip to the address table. ALTER is also used to modify and drop entities.
DROP INDEX	DROP INDEX addr_idx;	Deletes the index addr_idx.

Table 1.5 Data manipulation language examples

Data Manipulation Language
DML Statement	Example	Explanation
INSERT	INSERT INTO address VALUES (1234, ’56 Main St’, ’Seattle’, ’WA’);	Adds rows to the table with the specified values, which are in column order
UPDATE	UPDATE address SET state = ’OR’	Sets the value of the state column to ’OR’ for all rows
DELETE	DELETE FROM address WHERE state = ’OR’	Removes all rows that have the value ’OR’ in the state column

Table 1.6 Data query language examples

Data Query Language
DDL statement	Example	Explanation
SELECT … FROM	SELECT address_id, state FROM address	Returns the address_id and state values for all rows in the address table
SELECT … FROM … WHERE	SELECT address_id, state FROM address WHERE state = ’OR’	Returns the address_id and state values for all rows in the address table that have the value ’OR’ in the state column
SELECT … FROM … GROUP BY	SELECT state, COUNT(*) FROM address GROUP BY state	Returns the number of addresses in each state
SELECT … FROM … GROUP BY … HAVING	SELECT state, COUNT(*) FROM address GROUP BY state HAVING COUNT(*) > 50	Returns the number of addresses in each state that has at least 50 addresses