dplyr 힘과 데이터베이스 힘을 연결해서, dplyr 동사 혹은 SQL 질의문을 작성하여 최종 작업문을 통해 데이터프레임을 만들어 낸다. 즉, collect() 함수를 적용하여 최종 tbl_df() 데이터프레임을 생성시킨다.

1 속도와 저장공간 1

구글 제프딘이 모든 엔지니어가 알아야 되는 속도2에 대해 언급한 내용을 정리하면 다음과 같다.

작업 소요 시간
L1 캐쉬 참조 0.5 ns
L2 캐쉬 참조 7 ns
주기억장치 참조 100 ns
주기억장치에서 1 MB 순차적 읽어 오기 250,000 ns
디스크 검색 10,000,000 ns
네트워크에서 1 MB 순차적 읽어 오기 10,000,000 ns

\(10 GB\) 텍스트 파일이 있는 경우 전체 데이터를 순차적으로 불러 읽어들이는 경우 소요되는 시간은 다음과 같다.

  • 파일을 메모리로 올리는 경우 소요 시간
    • \(10 GB \times (250 \mu s / 1 MB) = 0.25초\)
  • 디스크에서 파일에 접근하는데 필요한 소요 시간
    • \(10 GB \times (250 \mu s / 1 MB) = 30초\)

만약 \(100 GB\) 크기 데이터 파일이 있는 경우는 다음과 같다.

  • 파일을 메모리로 올리는 경우 소요 시간
    • \(100 GB \times (250 \mu s / 1 MB) = 2.5초\)
  • 디스크에서 파일에 접근하는데 필요한 소요 시간
    • \(100 GB \times (250 \mu s / 1 MB) = 300초\)

일반적으로 메모리 비용이 훨씬 디스크 비용보다 비싼 반면에, 속도는 메모리가 디스크에 비해 훨씬 빠른다. 데이터가 커지게 되면 메모리를 크게하면 되지만, 메모리는 한정되어 확장성에 문제가 있다.

이에 대한 해법이 블록(block)을 생성하는 것인데 아이디어는 유사한 속성을 갖는 행을 집단으로 묶어 한번에 다수 행을 불러 읽어들이는 것이다. 이런 경우 최적 크기를 결정해야 되는데 풀고자 하는 문제에 따라 최적 크기를 결정하면 된다.

2 공공 데이터를 dplyr과 SQLite 작업 3 4

교통위반(Traffic Violations) 데이터를 read_csv 함수로 불러읽어 온다. 데이터프레임으로 불러읽어들이면 행이 826,934, 열이 35개가 되고 366 MB가 된다. 저장공간도 문제가 되고 나중에 작업을 위해 바이너리로 압축하여 저장하면 \(\frac{1}{10}\) 이상 크기가 줄어 27 MB까지 줄일 수 있다.

dplyr 데이터베이스

dplyr에서 데이터베이스에 연결하는 경우 src_sqlite(), src_mysql(), src_postgres(), src_bigquery()를 사용한다. 이렇게 생성된 객체는 내부에 데이터가 전혀없는 깡통이다. 데이터가 크지 않는 경우 copy_to 명령어를 사용해서 마치 R 데이터프레임처럼 원데이터베이스에서 R로 데이터를 흘린다.

# Source:   table<park_df> [?? x 36]
# Database: sqlite 3.22.0
#   [/Users/statkclee/swc/data-science/data/park_db.sqlite3]
   SeqID `Date Of Stop` `Time Of Stop` Agency SubAgency Description
   <chr> <chr>                   <dbl> <chr>  <chr>     <chr>      
 1 3258… 07/29/2019              82320 MCP    3rd Dist… DRIVER FAI…
 2 93dd… 07/29/2019              82080 MCP    2nd Dist… EXCEEDING …
 3 ec1d… 07/29/2019              81960 MCP    5th Dist… EXCEEDING …
 4 5f78… 07/29/2019              81960 MCP    6th Dist… STANDING, …
 5 7a7c… 07/29/2019              81840 MCP    2nd Dist… DRIVER CHA…
 6 2c8c… 07/29/2019              81480 MCP    6th Dist… DRIVING VE…
 7 c4f6… 07/29/2019              81300 MCP    2nd Dist… DRIVER FAI…
 8 9cde… 07/29/2019              81240 MCP    6th Dist… DRIVER FAI…
 9 4606… 07/29/2019              81240 MCP    3rd Dist… FAILURE TO…
10 3721… 07/29/2019              81240 MCP    2nd Dist… MOTOR VEH.…
# … with more rows, and 30 more variables: Location <chr>, Latitude <dbl>,
#   Longitude <dbl>, Accident <chr>, Belts <chr>, `Personal Injury` <chr>,
#   `Property Damage` <chr>, Fatal <chr>, `Commercial License` <chr>,
#   HAZMAT <chr>, `Commercial Vehicle` <chr>, Alcohol <chr>, `Work
#   Zone` <chr>, State <chr>, VehicleType <chr>, Year <dbl>, Make <chr>,
#   Model <chr>, Color <chr>, `Violation Type` <chr>, Charge <chr>,
#   Article <chr>, `Contributed To Accident` <int>, Race <chr>,
#   Gender <chr>, `Driver City` <chr>, `Driver State` <chr>, `DL
#   State` <chr>, `Arrest Type` <chr>, Geolocation <chr>

tbl(db, sql("SELECT * FROM park LIMIT 10")) 와 같이 db 연결에 sql() 인자를 넣어 직접 SQL 질의문을 실행시키는 것도 가능하다.

# Source:   SQL [?? x 36]
# Database: sqlite 3.22.0
#   [/Users/statkclee/swc/data-science/data/park_db.sqlite3]
   SeqID `Date Of Stop` `Time Of Stop` Agency SubAgency Description
   <chr> <chr>                   <dbl> <chr>  <chr>     <chr>      
 1 3258… 07/29/2019              82320 MCP    3rd Dist… DRIVER FAI…
 2 93dd… 07/29/2019              82080 MCP    2nd Dist… EXCEEDING …
 3 ec1d… 07/29/2019              81960 MCP    5th Dist… EXCEEDING …
 4 5f78… 07/29/2019              81960 MCP    6th Dist… STANDING, …
 5 7a7c… 07/29/2019              81840 MCP    2nd Dist… DRIVER CHA…
 6 2c8c… 07/29/2019              81480 MCP    6th Dist… DRIVING VE…
 7 c4f6… 07/29/2019              81300 MCP    2nd Dist… DRIVER FAI…
 8 9cde… 07/29/2019              81240 MCP    6th Dist… DRIVER FAI…
 9 4606… 07/29/2019              81240 MCP    3rd Dist… FAILURE TO…
10 3721… 07/29/2019              81240 MCP    2nd Dist… MOTOR VEH.…
# … with 30 more variables: Location <chr>, Latitude <dbl>,
#   Longitude <dbl>, Accident <chr>, Belts <chr>, `Personal Injury` <chr>,
#   `Property Damage` <chr>, Fatal <chr>, `Commercial License` <chr>,
#   HAZMAT <chr>, `Commercial Vehicle` <chr>, Alcohol <chr>, `Work
#   Zone` <chr>, State <chr>, VehicleType <chr>, Year <dbl>, Make <chr>,
#   Model <chr>, Color <chr>, `Violation Type` <chr>, Charge <chr>,
#   Article <chr>, `Contributed To Accident` <int>, Race <chr>,
#   Gender <chr>, `Driver City` <chr>, `Driver State` <chr>, `DL
#   State` <chr>, `Arrest Type` <chr>, Geolocation <chr>

dplyr 동사를 순차적으로 적용시킬 경우 지연연산(lazy evaluation)이 적용되어 바로 실행되지 않고, collect를 실행할 경우 한방에 실행된다. 결국 최종 질의문 혹은 dplyr 동사들이 완성되면 collect()를 사용하여 데이터베이스에서 데이터를 쭉 끌어내서 tble_df()을 생성시키는 작업흐름을 갖추게 된다.

# A tibble: 1,474,096 x 4
   Agency Accident Belts Geolocation                          
   <chr>  <chr>    <chr> <chr>                                
 1 MCP    No       No    (39.0196433333333, -77.0112633333333)
 2 MCP    No       No    (38.987855, -77.078515)              
 3 MCP    No       No    (39.1817616666667, -77.2369883333333)
 4 MCP    No       No    (39.17244, -77.2059066666667)        
 5 MCP    No       No    (39.0020183333333, -77.0771633333333)
 6 MCP    No       No    (39.1626433333333, -77.2249866666667)
 7 MCP    No       No    (38.9864533333333, -77.095275)       
 8 MCP    No       No    (39.19, -77.2201916666667)           
 9 MCP    No       No    (39.0314316666667, -77.02233)        
10 MCP    No       No    (39.0333316666667, -77.1257833333333)
# … with 1,474,086 more rows

  1. dplyr and SQL↩

  2. Numbers everyone should know↩

  3. dplyr Databases↩

  4. dplyr and SQL↩