해당 내용은 Datacamp의 Data engineering track을 정리했습니다.
Data Processing in Shell의 chapter 3에 대한 내용입니다.
해당 포스팅에는 아래의 내용을 포함하고 있습니다.
- sql2csv
- csvsql 데이터베이스에서 가져오기
- csvsql 데이터베이스로 보내기
1. Pulling data from databases
csvkit 라이브러리에서는 SQL과 유사한 기능인 sql2csv를 제공하고 있습니다. sql2csv는 Microsoft SQL, MySQL, Oracle, PostgreSQL, Sqlite를 포함하여 다양한 SQL 데이터베이스에 접근할 수 있습니다. sql2csv는 PgAdmin과 TablePlus와 같은 데이터베이스 클라이언트를 거치지 않고도 command-line을 통해 접근할 수 있다는 장점이 있습니다. command-line으로 sql2csv를 실행하면 출력 결과를 csv파일로 저장합니다.
# Syntax
sql2csv --db "sqlite:///SpotifyDatabase.db" \
--query "SELECT * FROM Spotify_Popularity" \
> Spotify_Popularity.csv쉘스크립트는 한 줄에 80자로 제한이 있기 때문에 명령이 너무 길다면 \를 활용해야 합니다. --db는 데이터베이스 연결방법을 알려주는 flag입니다. 연결하는 데이터베이스 위치의 정보를 포함하고 있습니다. sql2csv는 다양한 데이터베이스와 호환이 되기 때문에 연결하려는 데이터베이스에 따라 다르게 작성해줘야 합니다. sqlite는 sqlite:///로 시작해야 하며, 마지막에 .db를 붙여줘야 합니다. Postgres와 MySQL은 postgres:///, mysql:///로 시작하고 마지막에 .db는 붙여주지 않습니다.
query에는 원하는 query문을 작성해주면 됩니다. query문을 작성할 때에는 데이터베이스와 호환되는 SQL문을 사용해야 합니다. 쿼리 문자열은 쿼리의 길이나 복잡성에 관계없이 한 줄로 작성해야 합니다.
마지막으로 리디렉션을 하지 않으면, 해당 쿼리로 얻은 결과를 저장하지 못합니다. >를 활용해서 원하는 파일명으로 출력결과를 저장할 수 있습니다.
2. Manipulating data using SQL syntax
csvsql은 로컬에 저장된 csv 파일에 SQL과 유사한 명령문을 적용할 수 있는 방법입니다. 내부적으로 csvsql은 전체 csv 파일을 임시로 메모리 내 SQL 데이터베이스를 생성합니다. 그래서 편리하지만 대용량 파일 처리나 복잡한 SQL 쿼리를 적용하기에는 적합하지 않습니다.
만약, 특정 csv에서 첫번째 행만 출력하려고 한다면 다음과 같이 사용할 수 있습니다.
csvsql --query "SELECT * FROM Spotify_MusicAttributes LIMIT 1" \
Spotify_MusicAttributes.csv보기 좋은 형태로 출력하기 위해서는 뒤에 | csvlook을 적용하면 됩니다. 또한 출력된 결과를 저장하기 위해서는 리디렉션을 적용하면 됩니다. 다만, 리디렉션을 적용했을 때에는 화면에 결과를 출력하지 않습니다.
추가적으로, csvsql은 둘 이상의 csv파일에도 적용할 수 있습니다. 앞의 강의에서도 언급했듯이 SQL query는 항상 1줄에 표현되어야 하며, query에 사용된 파일을 모두 순서대로 적어줘야 합니다.
query문이 너무 긴 경우에는 따로 query문을 변수값에 할당한 뒤 "$할당한변수"로 쿼리를 대신 넣어줄 수 있습니다.
3. Pushing data back to database
지금까지는 데이터베이스로부터 데이터를 가져오는 작업을 진행했다면, 이번에는 데이터들을 다시 데이터베이스로 보내는 방법에 대해서 소개합니다. 앞에서 데이터를 불러올 때 사용했던 csvsql은 데이터를 데이터베이스로 보낼 때도 활용합니다. --insert, --db, --no-inference, --no-constraints 라는 flag를 활용합니다.
csvsql --db "sqlite:///SpotifyDatabase.db" \
--insert Spotify_MusicAttributes.csv--db에는 데이터베이스의 위치를 설정하기 위한 flag입니다. 앞서 sql2csv에서 사용했던 규칙을 여기서도 동일하게 적용하고 있습니다. --insert 옵션은 기존의 데이터베이스를 만드는 과정인 SQL 클라이언트 또는 사용자 인터페이스에 따라 빈 테이블을 만들고, 테이블 스키마를 지정하고, 테이블에 키를 지정한 다음 데이터를 작성하는 과정을 거쳐야 합니다. 이러한 과정을 --insert가 자체적으로 처리합니다. --insert는 항상 --db와 함께 사용되어야 합니다. 데이터베이스의 위치를 지정하지 않으면 데이터베이스에 데이터를 삽입할 수 없기 때문입니다.
물론 csvsql에서 스키마 유추하는 것이 항상 원하는대로 인식하지 않을 수 있습니다. 데이터에 텍스트 열로 취급되어야 하는 숫자 열이 있다면, --no-inference와 --no-constraints를 통해 미리 알려줄 수 있습니다. --no-constraints는 문자 길이 제한 없이 테이블을 생성할 수 있어서 큰 데이터 테이블에 유용하고 삽입 속도가 빨라지는 장점이 있습니다. 오류를 발생하지 않으며 모든 null 데이터를 포함하는 열을 생성할 수 있습니다.
