(SQL-Tutorial) 데이터 분석을 위한 SQL 레시피와 빅쿼리 사용

구글 클라우드, Google Cloud, 빅쿼리, BigQuery, BigQuery with Python
Page content

1줄 요약

데이터 분석을 위한 SQL 레시피 교재를 빅쿼리에서 활용해본다.

책 소개

실습 준비

  • 도서의 부록/예제소스를 다운로드 하세요.

  • 예제 소스 코드를 열어봅니다.

  • sql 소스코드로 구성이 되어 있는 것을 확인할 수 있습니다.

  • 저자가 말하는 샘플 데이터 내용은 아래와 같습니다.

  • 이번에는 임의의 SQL 파일을 열어서 확인하도록 합니다.

  • 위 이미지에서 보면, Table을 생성하는 형태로 구성이 되어 있는 것을 알 수 있습니다.
  • 따라서, 위 코드가 실제 빅쿼리에도 동일하게 적용이 되는지 확인을 하도록 합니다.

빅쿼리에서의 실습

  • 메뉴에서, 빅쿼리 API를 활성화 시킨 후, 쿼리 편집기에 들어갑니다.
  • 다음과 같은 샘플코드를 작성하여, 실제 결괏값이 나오는지 확인하도록 합니다.
SELECT
  COUNT(*) as total_rows
FROM `bigquery-public-data.samples.gsod`

  • 정상적으로 나왔다면 잘 진행이 된 것입니다.

(1) 에러의 향연

  • 이제, 교재의 sql 코드를 그대로 복사해서 실행하도록 합니다.
DROP TABLE IF EXISTS mst_users;
CREATE TABLE mst_users(
    user_id         varchar(255)
  , register_date   varchar(255)
  , register_device integer
);

INSERT INTO mst_users
VALUES
    ('U001', '2016-08-26', 1)
  , ('U002', '2016-08-26', 2)
  , ('U003', '2016-08-27', 3)
;

  • 에러가 납니다. 정확한 에러 메시지는 아래와 같이, mst_users라는 Table이 존재하지 않습니다.
    • 사실 당연합니다. mst_users 테이블을 만든적이 없으니까요. 그래서, 이번에는 DROP Table ~ 명령어를 삭제하고 CREATE TABLE ~ 명령어만 입력합니다.

Invalid value: Table name “mst_users” missing dataset while no default dataset is set in the request. at [1:1]

CREATE TABLE mst_users(
    user_id         varchar(255)
  , register_date   varchar(255)
  , register_device integer
);
  • 이번에도 마찬가지로 에러가 날 것입니다.
    • 왜 에러가 발생할까요? 이유는 sqlrecipe 프로젝트에 아직 데이터셋트를 생성하지 않았기 때문입니다. 즉, Database가 없는 상황인 것입니다. 그러면, 이번에는 Database명을 생성하도록 합니다.
  • 먼저 프로젝트에서 빨간색 원 안에 있는 버튼을 클릭한 후, 데이터 세트 만들기를 클릭합니다.

  • 필자는 데이터세트 명을 ch03으로 하였습니다.

  • 이번에는 Table명 앞에 데이터세트명을 추가해서 넣도록 합니다.
CREATE TABLE `ch03.mst_users`(
    user_id         varchar(255)
  , register_date   varchar(255)
  , register_device integer
);
  • 이번에는 다른 종류의 에러가 나옵니다. 즉, 테이블명은 해소가 되었습니다. 다만, varchar(255)에서 에러가 납니다.

  • 이런 경우에는 구글링도 중요하지만, 문법 문제일 가능성이 크기 때문에, 공식 홈페이지에서 확인하도록 합니다.
  • 두 SQL 유형을 살펴보면,
    • 문자열은 STRING에 해당하는 것을 확인할 수 있습니다. 즉, varchar(255) 대신에 STRING를 넣어주도록 합니다.
    • 또한 integer는 숫자 유형에 해당하는데, 빅쿼리에서는 INT64, NUMERIC, BIGNUMERIC, FLOAT64와 같이 표현을 합니다.
  • 즉, 종합하면 varchar(255)는 STRING로 변형해야 하고, integer는 INT64와 같이 변형해야 합니다.
CREATE TABLE `ch03.mst_users`(
    user_id         STRING
  , register_date   STRING
  , register_device INT64
);

  • 정상적으로 테이블이 생성된 것을 확인할 수 있습니다.
  • 이번에는 데이터를 넣어보도록 합니다.
INSERT INTO `ch03.mst_users`
VALUES
    ('U001', '2016-08-26', 1)
  , ('U002', '2016-08-26', 2)
  , ('U003', '2016-08-27', 3)
;

  • 이번에는 특별한 에러 없이 정상적으로 저장이 된 것을 확인할 수 있습니다.
  • 이제 본격적으로 교재와 함께 연습을 하도록 합니다.

(2) 교재 연습

  • 이번에는 교재의 샘플 코드를 직접 입력하도록 합니다.

SELECT
    user_id
    , CASE
        WHEN register_device = 1 THEN '데스크톱'
        WHEN register_device = 2 THEN '스마트폰'
        WHEN register_device = 3 THEN '애플리케이션'
        -- 디폴트 값을 지정할 경우 ELSE 구문을 사용합니다.
        -- ELSE ' '
      END AS device_name
FROM `sqlrecipe.ch03.mst_users`
  • 실제 빅쿼리에서도 잘 나오는지 확인하도록 합니다.

결론

  • 각 SQL마다 고유의 문법이 있습니다. 이러한 문법은 결국 공식 문서를 통해서 해결합니다.
  • 책은 모든 것을 다 담을수가 없습니다. 한계가 있지요. 기본적인 안내서이기 때문에, 빅쿼리에 해당하는 내용은 본 블로그를 통해서 해결하시기를 바랍니다.
  • 필자도 SQL 공부를 덜 하는 상태여서, 꾸준하게 포스팅을 할까 합니다.

Happy to Code