Data Engineer 가 알아야 할 SQL Part 1 (Korean)

Gyuhang ShimGyuhang Shim
17 min read

Table of contents

전제

  • ANSI SQL 을 어느정도 알고 있음.

  • ANSI SQL 의 모든 Syntax 를 다루지는 않음.

  • ANSI SQL 에서 사용하는 기본적인 Function 들에 대해서 지식을 가지고 있음.

ANSI SQL

SELECT

  • 데이터를 조회할 때 가장 기본적인 구문입니다.

  •   -- Syntax
  SELECT column1, column2 FROM table_name;
  -- Example: 직원들의 이름과 나이를 추출합니다.
  SELECT name, age FROM employees;

WHERE

  • 조건을 지정하여 데이터를 Filtering 하는 구문입니다.

  •   -- Syntax
  SELECT column1, column2 FROM table_name WHERE condition;
  -- Example: 나이가 30 보다 많은 직원들의 이름과 나이를 추출
  SELECT name, age FROM employees WHERE age > 30;

GROUP BY

  • 데이터를 그룹화하여 요약 정보를 계산할 때 사용합니다.

  •   -- Syntax
  SELECT column, AGGREGATE_FUNCTION(column) FROM table_name 
  GROUP BY column;
  -- Example: 직원들의 부서별로 grouping 하고 해당 부서와 부서 인원수를 추출
  SELECT department, COUNT(*) FROM employees GROUP BY department;

HAVING

  • GROUP BY 로 Grouping 된 데이터에 조건을 걸 때 사용됩니다.

  •   -- Syntax
  SELECT column, AGGREGATE_FUNCTION(column) FROM table_name
  GROUP BY column HAVING condition;
  -- Example: 직원 수가 5명 이상인 부서를 조회합니다.
  SELECT department, COUNT(*) FROM employees 
  GROUP BY department HAVING COUNT(*) > 5;

JOIN (통상적으로 INNER JOIN)

  • 두 개 이상의 Table 을 결합할 때 사용합니다.

  • INNER JOIN

    • 두 Table 간에 공통된 값을 기반으로 데이터를 결합하는 가장 일반적인 JOIN 유형입니다.

    • 두 Table 간에 지정된 조건이 일치하는 행만 결과에 포함되며, 조건에 일치하지 않는 데이터는 결과에서 제외됩니다.

  • INNER JOIN 을 사용해야 하는 경우

    • 두 Table 의 관계된 데이터를 결합할 때

      • 예를 들어, 하나의 Table 에 고객 정보가 있고, 다른 Table 에 주문 정보가 있을 때, 고객 ID 를 기준으로 결합하여 어떤 고객이 어떤 주문을 했는지 보여줄 수 있습니다.

    • 1:N Relation 을 처리할 때

      • 한 Table 이 '상위 Table'(예: 부서) 이고, 다른 Table 이 '하위 테이블'(예: 직원)일 때, 공통된 Column 을 기준으로 JOIN 하여 상위 데이터와 하위 데이터를 연결할 수 있습니다.

    • 데이터 Filtering

      • 두 Table 에 동일한 값을 가진 데이터만 필요할 때 사용합니다. 예를 들어, 특정 날짜에 발생한 거래와 그 거래에 관련된 고객 데이터를 얻고 싶을 때.

  • INNER JOIN 의 특징

    • 결과는 교집합: 두 Table 모두에 존재하는 공통된 데이터만 결과로 반환됩니다.

    • 결과의 행 수는 제한될 수 있음: 일치하지 않는 행은 결과에 포함되지 않기 때문에 데이터 양이 줄어들 수 있습니다.

    -- Syntax: INNER JOIN
    SELECT a.column1, b.column2 
    FROM table1 a 
    INNER JOIN table2 b ON a.common_column = b.common_column;
    -- Example: employees 테이블과 departments 테이블을 조인하여 
    -- 직원 이름과 부서 이름을 조회합니다.
    SELECT e.name, d.department_name 
    FROM employees e 
    INNER JOIN departments d ON e.department_id = d.department_id;

LEFT JOIN (또는 LEFT OUTER JOIN)

  • 왼쪽 Table 의 모든 데이터를 유지하고, 오른쪽 Table 에 Matching 되는 값이 없으면 NULL 로 표시됩니다.

  •   -- Syntax
  SELECT a.column1, b.column2 
  FROM table1 a 
  LEFT JOIN table2 b ON a.common_column = b.common_column;
  -- Example: 직원 테이블에 있는 모든 직원의 이름과, 
  -- 그들의 부서 이름(만약 부서가 없는 경우 NULL)을 반환합니다.
  SELECT e.name, d.department_name 
  FROM employees e 
  LEFT JOIN departments d ON e.department_id = d.department_id;

INNER JOIN vs LEFT JOIN

employees 테이블 (alias: e)

employee_idnamedepartment_id
1Alice10
2Bob20
3Charlie11
4Dave30

departments 테이블 (alias: d)

department_iddepartment_name
10HR
20IT
30Sales
40Marketing

INNER JOIN

SELECT e.name, d.department_name 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.department_id;

LEFT JOIN

SELECT e.name, d.department_name 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.department_id;

INNER JOIN 결과

# Charlie 의 department_id 11 에 해당하는 값이 departments table 에 없음.
+--------+------------------+
| name   | department_name   |
+--------+------------------+
| Alice  | HR               |
| Bob    | IT               |
| Dave   | Sales            |
+--------+------------------+

LEFT JOIN 결과

# LEFT JOIN 에서는 employees 테이블의 모든 행이 결과에 포함됨
# department_id 가 11(not match) 인 경우도 포함
+--------+------------------+
| name   | department_name  |
+--------+------------------+
| Alice  | HR               |
| Bob    | IT               |
| Charlie| NULL             |
| Dave   | Sales            |
+--------+------------------+

RIGHT JOIN 은 LEFT JOIN 의 반대라고 생각하면됨

UNION

  • 두 개의 SELECT Query 결과를 합칠 때 사용합니다.

  •   -- Syntax
  SELECT column1 FROM table1
  UNION
  SELECT column1 FROM table2;
  -- Example: 직원과 계약직 직원들의 이름을 하나의 리스트로 합칩니다.
  -- 중복된 값은 제거됩니다.
  SELECT name FROM employees
  UNION
  SELECT name FROM contractors;

INSERT

  • Table 에 데이터를 insert 할 때 사용합니다.

  •   -- Syntax
  INSERT INTO table_name (column1, column2)
  VALUES (value1, value2);
  -- Example: 새로운 직원 데이터를 테이블에 insert 합니다.
  INSERT INTO employees (name, age, department_id)
  VALUES ('John', 30, 1);

UPDATE

  • Table 의 데이터를 update 할 때 사용합니다.

  •   -- Syntax: 보통 condition 이 같이 사용됨
  UPDATE table_name 
  SET column1 = value1, column2 = value2 
  WHERE condition;
  -- Example: 이름이 'John' 인 직원의 나이를 31 로 수정합니다.
  UPDATE employees 
  SET age = 31 
  WHERE name = 'John';

DELETE

  • Table 의 데이터를 delete 할 때 사용합니다.

  •   -- Syntax: 보통 condition 이 같이 사용됨
  DELETE FROM table_name WHERE condition;
  -- Example: 이름이 'John' 인 직원을 삭제합니다.
  DELETE FROM employees WHERE name = 'John';

CREATE TABLE

  • 새로운 Table 을 생성할 때 사용합니다.

  •   -- Syntax
  CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
  );
  -- Example: employees table 생성, id 는 정수형, name 은 문자열
  -- age 는 정수형, department_id 는 정수형 으로 schema 를 정의
  CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department_id INT
  );

ALTER TABLE

  • 기존 Table 의 schema 를 수정할 때 사용합니다.

  •   -- Syntax
  ALTER TABLE table_name ADD column_name datatype;
  -- Example: employees 테이블에 salary 라는 정수형 column 을 추가합니다.
  ALTER TABLE employees ADD salary INT;

TRUNCATE

  • Table 의 데이터를 모두 삭제하지만, Table schema 는 남겨둡니다.

  •   -- Syntax
  TRUNCATE TABLE table_name;
  -- Example: employees table 의 모든 데이터를 삭제합니다.
  TRUNCATE TABLE employees;

INDEX

  • Query 성능을 높이기 위해 index 를 생성할 때 사용합니다.

  •   -- Syntax
  CREATE INDEX index_name ON table_name (column1);
  -- Example: employees 테이블의 name 컬럼에 
  -- 인덱스를 생성하여 검색 성능을 향상시킵니다.
  CREATE INDEX idx_employee_name ON employees (name);

CASE

  • 조건에 따라 다른 값을 반환할 때 사용합니다.

  •   -- Syntax
  SELECT column1,
         CASE 
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE result3
         END
  FROM table_name;
  -- Example: 직원의 나이에 따라 'Young', 'Middle-aged', 'Senior' 로 
  -- 그룹화하여 결과를 출력합니다.
  SELECT name, 
         CASE 
           WHEN age < 30 THEN 'Young'
           WHEN age BETWEEN 30 AND 50 THEN 'Middle-aged'
           ELSE 'Senior'
         END AS age_group
  FROM employees;

Trino SQL

ANSI SQL 과 Trino SQL 의 공통점

Trino 는 대부분의 ANSI SQL Syntax 를 지원 하므로 기본적인 SQL 작업은 둘 사이에 큰 차이가 없습니다. 다음과 같은 일반적인 SQL Syntax 는 Trino 에서도 동일하게 동작합니다:

  • SELECT, INSERT, UPDATE, DELETE

  • JOIN, GROUP BY, HAVING, ORDER BY

  • WHERE 조건을 사용한 데이터 Filtering

  • CASE 를 사용한 Conditional Expression

  • DISTINCT, LIMIT 등을 사용한 데이터 제한

Trino SQL 의 고유 확장 및 차이점

Trino 는 대규모 분산 데이터 처리와 여러 Data Source 에 대한 Query 최적화를 위해 ANSI SQL 과는 다른 고유한 SQL Syntax 및 기능을 제공합니다.

  • 대용량 Data Set 에서 근사치를 제공하는 집계 (Aggregation) Function

    • i.e APPROX_DISTINCT, approx_percentile()

  • 복잡한 데이터 Type 지원

    • i.e MAP, ARRAY

  • 분산된 unstructured Data Source 에 대한 SQL Query 지원

  • UNNEST 와 같은 중첩 데이터 처리 기능

  • 다양한 Timezone 처리를 위한 AT TIME ZONE Syntax

APPROX_DISTINCT()

  • Trino 는 대용량 Data Set 에서 정확한 집계를 수행하는 것이 비용이 많이 들 수 있기 때문에 근사치를 제공하는 함수를 제공합니다.

  • APPROX_DISTINCT 는 많은 데이터에서 고유 값을 빠르게 계산하는 함수입니다.

  •   -- web_log 테이블에서 고유 사용자 수를 근사적으로 계산합니다. 
  -- 대용량 데이터에서 고유 사용자 수를 정확하게 계산하는 것보다 성능이 훨씬 빠릅니다.
  SELECT APPROX_DISTINCT(user_id) AS distinct_users FROM web_log;

WITH 구문 (Common Table Expressions, CTE)

  • ANSI SQL 에서도 WITH 구문을 사용할 수 있지만, Trino에서는 WITH 구문을 사용하여 복잡한 Query 를 좀 더 쉽게 작성하고 관리할 수 있습니다.

  • 특히 Trino 에서 여러 Data Source 에 대해 동일한 Logical View 를 적용할 때 유용합니다.

  •   -- create employee_salary logical view and extract name, salary
  WITH employee_salary AS (
      SELECT name, salary
      FROM employees
      WHERE department = 'Engineering'
  )
  SELECT name, salary
  FROM employee_salary
  WHERE salary > 100000;

approx_percentile()

  • Trino 는 대규모 Data Set 에서 근사적으로 백분위수를 계산할 수 있는 함수도 제공합니다.

  • 일반적인 통계 분석에 유용하며 대규모 데이터에서 빠른 결과를 얻을 수 있습니다.

  •   -- 이 query 는 employees table 에서
  -- 급여의 90 번째 백분위수를 근사적으로 계산합니다.
  SELECT approx_percentile(salary, 0.9) AS percentile_90
  FROM employees;

FROM UNNEST

  • UNNEST 는 Array 또는 중첩된 데이터 구조를 평평하게 (flatten) 만드는 Trino 의 고유한 기능입니다.

  • Trino 에서는 복잡한 데이터 구조를 쉽게 다룰 수 있습니다.

  •   -- 여기서 skills는 Array 필드이고, 
  -- 이를 각 직원별로 개별적인 행으로 분리하여 조회합니다.
  SELECT name, skill
  FROM employees, UNNEST(skills) AS t(skill);

  • Query 결과 예제

    employees table

    | name | skills | | --- | --- | | Alice | ['Python', 'Java'] | | Bob | ['Scala'] | | Charlie | ['Python', 'Scala', 'Go'] |

  • 실행 결과

  •   # Alice 는 Python 과 Java 라는 두 가지 스킬을 가지고 있으므로 두 개의 행으로 나뉩니다.
  # Bob 은 Scala 스킬만 가지고 있으므로 한 행만 생성됩니다.
  # Charlie 는 Python, Scala, Go 세 가지 스킬을 가지고 있어, 세 개의 행으로 변환됩니다.
  +---------+--------+
  | name    | skill  |
  +---------+--------+
  | Alice   | Python |
  | Alice   | Java   |
  | Bob     | Scala  |
  | Charlie | Python |
  | Charlie | Scala  |
  | Charlie | Go     |
  +---------+--------+

MAP 및 ARRAY 데이터 타입

  • Trino 는 MAP 과 ARRAY 데이터 Type 을 직접 지원합니다.

  • 이는 JSON, NoSQL 같은 unstructured 데이터를 처리할 때 유용합니다.

  • ANSI SQL 에서는 이러한 데이터 Type 을 처리하는 데 제한이 있지만, Trino 에서는 이러한 복잡한 데이터 구조를 Query 할 수 있습니다.

  •   SELECT id, array[1, 2, 3] AS sample_array,
  map(array['a', 'b'], array[1, 2]) AS sample_map
  FROM employees;

  • Query 결과 예제

  • employees table

    | id | name | | --- | --- | | 1 | Alice | | 2 | Bob | | 3 | Charlie |

  •   # sample_array: 모든 행에 동일한 Array [1, 2, 3] 이 생성됩니다.
  # Array 는 각 직원에 대해 동일하게 반환됩니다.
  # sample_map: map(array['a', 'b'], array[1, 2])는 두 개의 배열을 사용하여 
  # Map 을 생성합니다. 
  # Array ['a', 'b']가 key 가 되고, Array [1, 2]가 value 가 되어 
  # {'a' -> 1, 'b' -> 2} 라는 Map 이 생성됩니다. 
  # 이 Map 도 각 직원에 대해 동일한 결과로 반환됩니다.
  # id 는 employees table 에서 가져온 고유한 직원 ID 입니다.
  +----+--------------+---------------------------+
  | id | sample_array | sample_map                |
  +----+--------------+---------------------------+
  | 1  | [1, 2, 3]    | {'a' -> 1, 'b' -> 2}      |
  | 2  | [1, 2, 3]    | {'a' -> 1, 'b' -> 2}      |
  | 3  | [1, 2, 3]    | {'a' -> 1, 'b' -> 2}      |
  +----+--------------+---------------------------+

AT TIME ZONE

  • Trino 는 Timezone 변환을 지원하는 AT TIME ZONE Syntax 를 통해 전 세계의 다양한 Timezone 을 처리할 수 있습니다.

  •   -- 이 query 는 orders 테이블에서 order_time 을 
  -- 미국 뉴욕 시간대로 변환하여 반환합니다.
  SELECT order_time AT TIME ZONE 'America/New_York' AS local_time
  FROM orders;

SQL on Non-relational Data Sources

  • Trino 는 분산 SQL 엔진으로, 다양한 Data Source 를 Query 할 수 있다는 점에서 ANSI SQL과 큰 차이점을 보입니다.

  • ANSI SQL 은 일반적으로 관계형 Database 에 적용되지만, Trino 는 다음과 같은 다양한 Data Source 를 지원합니다.

    • Hive

    • Hadoop HDFS

    • S3

    • Kafka

    • MongoDB

  •   -- Example: S3 에서 데이터 query
  -- 다음 query 는 Hive 메타스토어를 통해 S3 에서 저장된 데이터를 조회합니다.
  SELECT * FROM hive.default.logs WHERE date = '2024-01-01';

Impala SQL

ANSI SQL 과 Impala SQL 의 공통점

Impala 는 ANSI SQL 과 거의 동일한 방식으로 작동하며, 대부분의 표준 SQL Syntax 를 지원합니다. 다음과 같은 기본 SQL Syntax 는 ANSI SQL 과 Impala 에서 동일하게 사용할 수 있습니다. (Trino 와 동일)

  • SELECT, INSERT, UPDATE, DELETE

  • JOIN, GROUP BY, HAVING, ORDER BY

  • WHERE 조건을 통한 데이터 Filtering

  • CASE 를 사용한 Conditional Expression

  • DISTINCT, LIMIT 등을 사용한 데이터 제한

Impala SQL 의 고유 확장 및 차이점

  • Impala 는 대규모 데이터 처리와 관련된 고유한 최적화와 확장된 SQL Syntax 를 제공합니다.

  • ANSI SQL 에서 사용되지 않는 몇 가지 기능이 추가되어 있으며, 이는 주로 성능 최적화와 대용량 데이터 처리를 위해 제공됩니다.

Partitioning 과 Clustering 최적화

  • Impala 는 HDFS 에 저장된 데이터에 대해 매우 큰 Table 을 효율적으로 Query 할 수 있도록 Partitioning 과 Clustering 을 지원합니다. 이러한 기능은 대규모 Data Set 에서 성능 최적화를 위해 자주 사용됩니다.

  • CREATE TABLE with Partitioning: Impala 에서 Table 을 생성할 때 Partitioning 을 적용하여 데이터를 관리하고, Query 성능을 향상시킬 수 있습니다.

  •   -- 이 Table 은 year 와 month 기준으로 데이터를 paritioning 하여 저장합니다.
  CREATE TABLE sales (
    sale_id INT,
    sale_date STRING,
    amount DOUBLE
  )
  PARTITIONED BY (year STRING, month STRING);

  •   +---------+------------+--------+------+-------+
  | sale_id | sale_date  | amount | year | month |
  +---------+------------+--------+------+-------+
  | 1       | 2024-01-15 | 250.50 | 2024 | 01    |
  | 2       | 2024-02-10 | 150.75 | 2024 | 02    |
  | 3       | 2023-11-05 | 300.00 | 2023 | 11    |
  +---------+------------+--------+------+-------+

  •   # HDFS 에서의 Impala Query 로 생성된 Table 의 file 구조
  /hdfs/path/to/sales/
  │
  ├── year=2023/
  │   ├── month=11/
  │   │   ├── part-00001.parquet
  │   │   ├── part-00002.parquet
  │   │   └── ...
  │
  ├── year=2024/
  │   ├── month=01/
  │   │   ├── part-00001.parquet
  │   │   ├── part-00002.parquet
  │   │   └── ...
  │   ├── month=02/
  │   │   ├── part-00001.parquet
  │   │   └── ...
  │
  └── ...

KUDU Table 지원

  • Impala 는 Kudu 라는 분산 스토리지를 지원하며, Kudu Table 은 Impala SQL 에서 다룰 수 있습니다.

  • Kudu 는 빠른 Random Access 와 실시간 분석이 가능하게 하므로, Impala 와 함께 사용하면 실시간 데이터 처리가 필요한 상황에 적합합니다.

  • CREATE TABLE with Kudu

  •   -- Kudu 스토리지를 사용하여 테이블을 생성하는 방법으로, 
  -- 빠른 읽기/쓰기 성능을 제공합니다.
  CREATE TABLE employees (
    id INT PRIMARY KEY,
    name STRING,
    age INT
  )
  STORED AS KUDU;

COMPUTE STATS

  • Impala 는 통계 기반의 최적화 기능을 제공하여 Query Execution Plan 을 개선할 수 있습니다.

  • 데이터를 읽기 전에 테이블 통계를 수집하는 COMPUTE STATS 명령어를 사용하면 Impala 의 성능을 최적화할 수 있습니다.

  •   -- employees table 의 통계를 수집하여 query 성능을 최적화합니다.
  COMPUTE STATS employees;

INSERT OVERWRITE

  • Impala 에서는 Table 에 데이터를 insert 하거나 overwrite 할 수 있습니다.

  • ANSI SQL 에서도 데이터 삽입과 업데이트가 가능하지만, Impala는 대규모 데이터를 효율적으로 다루기 위해 INSERT OVERWRITE Syntax 를 제공합니다.

  •   -- Example: sales table 의 특정 partition 을 새로운 데이터로 덮어씁니다.
  INSERT OVERWRITE TABLE sales PARTITION (year='2024', month='10')
  SELECT * FROM new_sales WHERE year = '2024' AND month = '10';

SHOW FILES

  • Impala 는 HDFS 파일을 직접 조회할 수 있는 명령어를 제공합니다.

  • 이는 Impala 가 분산 파일 시스템 상의 데이터를 어떻게 접근하고 관리하는지 확인할 수 있는 기능입니다.

  •   -- Example: my_table 에 저장된 파일을 조회하여 
  -- 실제 HDFS 에 저장된 파일 구조를 확인할 수 있습니다.
  SHOW FILES IN my_table;

SHOW TABLE STATS 및 SHOW COLUMN STATS

  • Impala 는 Table 및 Column Statistics 를 제공하여 Query 최적화에 도움을 줍니다.

  • Impala 에서 제공하는 통계 조회 기능은 대규모 데이터를 처리할 때 매우 유용합니다.

  •   -- Examples
  SHOW TABLE STATS employees;
  SHOW COLUMN STATS employees;

IMPALA SHELL (CLI 특화 기능)

  • Impala 는 Command Line Interface (CLI) 에서 Query 를 실행할 수 있는 impala-shell이라는 특화된 기능을 제공합니다.

  • 이를 통해 대규모 Data Set 에 대해 다양한 Query 를 효율적으로 실행하고, 결과를 확인할 수 있습니다.

PARQUET 및 ORC Format 의 최적화

  • Impala 는 특히 PARQUET 과 ORC 같은 Column 기반 저장 Format 에 대해 강력한 최적화를 제공합니다.

  • ANSI SQL 은 저장 Format 에 대한 구체적인 지원이 없지만, Impala 는 이러한 대규모 데이터 Format 을 빠르게 처리할 수 있습니다.

  •   CREATE TABLE sales_parquet (
    sale_id INT,
    sale_date STRING,
    amount DOUBLE
  )
  STORED AS PARQUET;

HDFS 와의 연동

  • Impala 는 HDFS 과 밀접하게 통합되어 있습니다.

  • Impala SQL 은 HDFS 에 저장된 데이터를 직접 Query 할 수 있기 때문에 매우 큰 데이터를 효율적으로 분석할 수 있습니다.

  •   -- HDFS 에 저장된 테이블에서 특정 연도의 데이터를 조회합니다.
  SELECT * FROM hdfs_table WHERE year = '2024';

Impala, Trino 에서 INSERT OVERWRITE Syntax 를 지원하는 이유

  • 데이터 분석 시 자주 발생하는 요구 중 하나가 데이터를 Update 이다.

    • 이는 특히 정적 Table 이나 주기적으로 갱신되는 Partition 에서 유용합니다.

  • 특정 Partition 의 데이터를 교체할 때 매우 유용합니다.

    • Partition 은 시간, 지리적 위치 등의 기준으로 나뉘어 관리 되므로, 시간이 지남에 따라 특정 Partition 의 데이터를 Update 해야 하는 상황이 자주 발생합니다.

    • INSERT OVERWRITE 는 해당 Partition 내의 기존 데이터를 삭제하고 새로운 데이터로 덮어쓰는 방식으로 동작합니다.

  • INSERT OVERWRITE 는 단일 작업으로 기존 데이터를 완전히 덮어 쓰면서 동시성 문제나 불완전한 데이터 적재 문제를 줄일 수 있습니다. (Data Consistency 유지)

  • 데이터를 주기적으로 초기화하거나 재처리 할 필요가 있는 경우, INSERT OVERWRITE 는 기존 데이터 Delete 와 Insert 를 결합한 간단한 명령어로 처리할 수 있습니다. 별도의 삭제 작업 없이 새로운 데이터로 교체할 수 있어 관리와 처리 과정이 단순화됩니다.

  • 대규모 Batch Processing 환경에서는 데이터의 일괄 갱신이 필요합니다. 주기적인 Batch 작업에서 기존 데이터를 Delete 하고 새로운 데이터를 Insert 하는 방식은 번거로울 수 있는데, INSERT OVERWRITE 는 이 작업을 한 번에 해결 해 줍니다.

INSERT OVERWRITE 의 단점

  • INSERT OVERWRITE 는 기존 데이터를 삭제하고 새로운 데이터를 쓰는 방식 이므로, 전체 데이터를 다시 쓰는 데 비용이 발생할 수 있습니다. 작은 변경 사항이 있을 때도 전체 파티션 또는 테이블이 덮어써지기 때문에, 불필요한 쓰기 작업이 늘어날 수 있습니다.

  • INSERT OVERWRITE 는 기존 데이터를 완전히 덮어 쓰므로, 의도치 않게 잘못된 데이터로 덮어쓸 경우 데이터 손실의 위험이 있습니다.

  • 동시에 여러 Transaction 이 INSERT OVERWRITE 를 수행할 경우, 경합이 발생할 수 있습니다. 특히 대규모 데이터에서 여러 작업이 병렬로 처리될 때 Locking 과 동시성 문제가 생겨 성능이 저하될 수 있습니다.

  • 자주 데이터를 교체해야 하는 환경에서는 INSERT OVERWRITE 가 과도하게 사용될 경우, 시스템 Resource 사용이 증가할 수 있습니다. 특히, 정기적인 Batch Processing 에서 Partition 을 자주 갱신하는 경우 성능에 영향을 미칠 수 있습니다.

SQL 용어 모음

DDL (Data Definition Language)

  • DDL 은 데이터베이스 Object(Table, Index, View 등)를 정의하고 수정하는 데 사용됩니다.

  • 주요 명령어

    • CREATE: 새로운 Database Object (예: Table)를 생성.

    • ALTER: 기존 Database Object 를 수정.

    • DROP: Database Object 를 삭제.

    • TRUNCATE: Table 의 모든 데이터를 삭제하지만 Table 자체는 유지.

  •   -- Example
  CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100)
  );

DML (Data Manipulation Language)

  • DML 은 Database 내에서 데이터를 조작하고 관리하는 데 사용됩니다.

  • 주요 명령어

    • INSERT: 데이터를 Table 에 insert.

    • UPDATE: 기존 데이터를 update.

    • DELETE: Table 에서 데이터를 delete.

    • SELECT: 데이터를 조회.

  •   -- Example
  INSERT INTO employees (id, name, position) 
  VALUES (1, 'John Doe', 'Manager');

DCL (Data Control Language)

  • DCL 은 Database 에 대한 권한을 제어하는 명령어를 포함합니다.

  • 주요 명령어

    • GRANT: 사용자가 특정 권한을 갖도록 허용.

    • REVOKE: 사용자의 권한을 회수.

  •   -- Example
  GRANT SELECT ON employees TO user1;

TCL (Transaction Control Language)

  • TCL 은 Transaction 을 관리하는 명령어입니다.

    • Transaction 은 여러 SQL 작업을 묶어 하나의 작업 단위로 처리합니다.

  • 주요 명령어

    • COMMIT: Transaction 을 확정 (commit) 하고 변경 내용을 영구적으로 저장.

    • ROLLBACK: Transaction 을 취소하고 변경 내용을 되돌림 (rollback).

    • SAVEPOINT: Transaction 내에 중간 저장점(savepoint) 을 설정하여 부분적으로 롤백 가능.

  •   -- Example: Transaction begin, update, commit
  BEGIN;
  UPDATE employees SET position = 'Senior Manager' WHERE id = 1;
  COMMIT;

DQL (Data Query Language)

  • DQL 은 데이터를 조회하는 명령어로, 사실상 SELECT 명령어를 가리킵니다.

  • 주요 명령어

    • SELECT: Table 에서 데이터를 조회.
  •   SELECT * FROM employees WHERE position = 'Manager';

Constraints (제약 조건)

  • Table 에 적용되어 데이터 무결성 (Integrity) 을 보장하는 규칙입니다.

  • 주요 Constraints

    • PRIMARY KEY: 유일한 식별자. 중복되지 않고, NULL 값을 가질 수 없음.

    • FOREIGN KEY: 다른 Table 의 기본 키를 참조하는 외래 키.

    • UNIQUE: 중복된 값이 허용되지 않음.

    • NOT NULL: 해당 field 에 NULL 값이 들어갈 수 없음.

    • CHECK: 특정 조건을 만족해야 하는 field 값에 constraint 를 거는 데 사용.

  •   -- Example: PRIMARY KEY, FOREIGN KEY 생성
  CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(product_id)
  );

Index

  • Index 는 Table 에서 데이터를 더 빠르게 조회할 수 있도록 하는 구조입니다.

  •   CREATE INDEX idx_employee_name ON employees(name);

Views

  • View 는 저장된 SQL Query 결과를 가상 Table 로 정의한 것입니다.

  • 물리적으로 데이터를 저장하지 않지만, 마치 Table 처럼 데이터를 조회할 수 있습니다.

  •   CREATE VIEW manager_view AS
  SELECT id, name FROM employees WHERE position = 'Manager';

Normalization (정규화)

  • 데이터 중복을 최소화하고 데이터베이스의 구조를 효율적으로 설계하기 위한 방법론입니다.

  • Table 을 작은 관계형 구조로 분해하여 일관성 있게 유지합니다.

  • 주로 Relational Database 에서 많이 취하는 방식 입니다.

  • 대규모 Dataset 을 분석 Query 로 조회하기 위해서는 Normalization 된것을 역으로 중복을 허용하여 De-Normalization 을 진행하여, 조회 속도를 올리는 Use Case 도 있습니다.

Joins

  • 두 개 이상의 Table 을 연결해 데이터를 조회하는 방법입니다.

  • 주요 JOIN 유형

    • INNER JOIN: 두 Table 간 공통된 부분만을 반환.

    • LEFT JOIN: 왼쪽 Table 의 모든 데이터와 오른쪽 Table 의 일치하는 데이터를 반환.

    • RIGHT JOIN: 오른쪽 Table 의 모든 데이터와 왼쪽 Table 의 일치하는 데이터를 반환.

    • FULL JOIN: 두 Table 의 모든 데이터를 반환하고, 일치하지 않는 경우 NULL 로 표시.

  •   SELECT employees.name, departments.department_name
  FROM employees
  INNER JOIN departments ON employees.department_id = departments.id;
0
Subscribe to my newsletter

Read articles from Gyuhang Shim directly inside your inbox. Subscribe to the newsletter, and don't miss out.

ANSI SQLdata engineerSQLtrinoImpala

Written by

Gyuhang Shim
Gyuhang Shim

Gyuhang Shim Passionate about building robust data platforms, I specialize in large-scale data processing technologies such as Hadoop, Spark, Trino, and Kafka. With a deep interest in the JVM ecosystem, I also have a strong affinity for programming in Scala and Rust. Constantly exploring the intersections of high-performance computing and big data, I aim to innovate and push the boundaries of what's possible in the data engineering world.