게시 됨 업데이트 됨PostgreSQL / Docker

Postgresql 데이터 csv로 저장 및 csv데이터 insert하기

쿼리결과 csv 파일로 저장하기

Database: Postgresql
실행환경: Docker

  1. psql 접속하여 실행하는 법

    1
    2
    3
    4
    5
    6
    7
    docker exec -it <데이터베이스 컨테이너> bash

    # 컨테이너 접속 후
    psql -U <name> -d <database>

    # psql 접속 후
    COPY (select * from <tablename>) To '/path/fiilename.csv' With CSV DELIMITER ',' HEADER;

  2. 스크립트 작성으로 만들기

    1
    2
    3
    4
    5
    6
    docker exec <데이터베이스 컨테이너> \
    su - postgres -c \
    '
    psql -U <username> -d "<database name>" \
    -c "COPY (select * from <table name>) To '"'/path/fiilename.csv'"' With CSV DELIMITER '"','"' HEADER; "
    '

    주의사항: 따옴표 내부에서 문자열 조건이나 경로 입력 시 ‘“‘문자열’”‘로 감싸서 작성해야 문자열로 인식한다.

csv 파일 import 하기

  1. psql 접속하여 실행하는 법

    1
    2
    3
    4
    5
    6
    7
    docker exec -it <데이터베이스 컨테이너> bash

    # 컨테이너 접속 후
    psql -U <name> -d <database>

    # psql 접속 후
    COPY <tablename> FROM '/path/filename.csv' DELIMITER ',' CSV HEADER;

  2. 스크립트 작성으로 만들기

    1
    2
    3
    4
    5
    6
    docker exec <데이터베이스 컨테이너 이름> \
    su - postgres -c \
    '
    psql -U <username> -d "<database name>" \
    -c "COPY <table name> FROM '"'/path/filename.csv'"' DELIMITER '"','"' CSV HEADER; "
    '

스크립트 작성 중 알게 된 것
도커로 실행하는 레일스 앱에 스크립트에서 명령을 실행하기위해서는 다음과 같이 작성한다. 

1
2
3
docker exec -i <container> rails c <<EOF
# 실행하고 싶은 명령어
EOF
 게시 됨 업데이트 됨Ruby / PostgreSQL / Docker / Ruby on Rails / docker-compose

Ruby on Rails Docker Setting (레일스 도커 개발환경 세팅하기)

Ruby on Rails Docker Setting

레일스 개발 환경을 도커로 세팅해보자

크게 복잡하고 거창한 환경 세팅은 아니지만 처음 세팅이 항상 골칫거리이다..

개발 환경
ruby-on-rails -v 7.0^
ruby -v 2.7.1
RubyMine
Docker
docker-compose
postgres -v 14.2-alpine

1. 기본 세팅을 위한 파일 생성 및 작성

1
2
3
4
5
6
mkdir backend
cd backend

touch Dockerfile
touch docker-compose.yml
touch docker-compoes.env

Dockerfile 작성 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# syntax=docker/dockerfile:1
FROM ruby:2.7.1

RUN apt-get update -qq && apt-get install -y nodejs postgresql-client

WORKDIR /usr/src/app
COPY Gemfile ./
COPY Gemfile.lock ./
RUN bundle install

# Add a script to be executed every time the container starts.
COPY entrypoint.sh /usr/bin/
RUN chmod +x /usr/bin/entrypoint.sh
ENTRYPOINT ["entrypoint.sh"]
EXPOSE 3000

# Configure the main process to run when running the image
CMD ["rails", "server", "-b", "0.0.0.0"]

docker-compose.yml 작성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
version: "3"
services:
  ## backend
  database:
    image: postgres:14.2-alpine
    ports:
      - "5432:5432"
    env_file: docker-compose.env
    volumes:
      - ./psql/data:/var/lib/postgresql/data

  ## api
  web:
    container_name: web
    build: .
    command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'"
    ports:
      - "3000:3000"
    env_file: docker-compose.env
    volumes:
      - ./:/usr/src/app
    depends_on:
      - "database"
    environment:
      - RAILS_ENV=development

Gemfile, Gemfile.lock 생성 

1
2
touch Gemfile
touch Gemfile.lock
1
2
3
4
# Gemfile
source "https://rubygems.org"

ruby "2.7.1"

docker-compose.env

1
2
3
4
5
POSTGRES_USER=user
POSTGRES_PASSWORD=password

PG_USER=user
PG_PASSWORD=password

2. 빌드 시작

1
docker-compose build

빌드를 시작해도 아직 레일스 세팅이 안되어있다. docker 컨테이너를 이용하여 레일스 설치를 진행한다.

1
docker-compose run --no-deps web rails new . --api --force --database=postgresql

실행 시 뭔가 쭉쭉 설치되고 로컬 디렉토리와 컨테이너 볼륨을 지정해줬기 때문에 작업 디렉토리에 레일스 폴더, 파일들이 생성된다.



3. database.yml 작성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# ./config/database.yml

default: &default
  adapter: postgresql
  encoding: utf8
  # For details on connection pooling, see Rails configuration guide
  # https://guides.rubyonrails.org/configuring.html#database-pooling
  pool: 5
  host: database # docker 환경 데이터베이스와 연결을 하기위해서는 컨테이너 이름으로 지정해주어야 한다.
  username: juren
  password: juren

development:
  <<: *default
  database: juren_development

test:
  <<: *default
  database: juren_test

4. 데이터베이스 생성하기

아직 도커에서 실행중인 postgresql 에는 데이터베이스가 생성되어있지 않다. yml 파일에서 지정한 데이터베이스들을 생성해주기 위해서는 아래 명령어를 실행한다.

1
2
docker-compsoe up # 컨테이너 실행 명령어
docker-compose run web rake db:create # rails db 생성 명령어

5. localhost 접속

크롬 -> localhost:3000 접속 결과 확인



끝~👍👍

은 아니고 이제 시작...
 게시 됨 업데이트 됨PostgreSQL

Postgresql ROW _NUMBER 함수

Postgresql DB row_number() 함수

row_number 함수는 조회하는 데이터의 특정 집합내에서 select 결과의 건수의 변화없이 데이터를 조회하는 함수이다.

데이터베이스에서 상품 테이블과 이미지 테이블을 조인한 데이터를 가져오려고 하는 과정에서 문제가 한가지 발행하였다. 현재 개발중인 API서버에서 상품의 데이터들을 조회하여 15개씩 limit옵션을 사용하여 가져오려고 한다.
데이터베이스에 대한 심도있는 학습을 하지 않았던 탓일까 왜 당연히 15개의 상품이 모두 조회된다는 생각을 했던것일까…

기존 쿼리 및 결과

정말 쉽게 생각해서 간단하게 left join만 해주면 되는 문제다라는 생각으로 데이터베이스에 조회를 했다. 내가 작성한 쿼리는 다음과 같다.

  • sql

    1
    2
    3
    4
    5
    6
    		 
    select p.product_no, p.product_title, i.image_no, i.image_src
    from product p
    left join image i 
    on p.product_no = i.image_product_no
    order by product_no desc
    limit 15;

  • 결과

15개의 데이터를 가져오기는 했다… 하지만 이것은 내가 가져오고자 한 데이터의 모든 정보를 가져와 주지않았다. 이렇게 쿼리를 작성해보니 당연한 결과였는데 어떻게 그렇게 단순하게 생각을 한건지 나에게 부끄러움을 느꼈다…

그래서 데이터베이스를 잘아는 분과 구글의 힘을 빌려 원하는 데이터를 가져올 수 있는 방법을 찾던중 postgresql이 orcle의 ROWNUM함수와 같은 기능의 함수를 제공한다는 정보를 통해 쿼리문을 수정하였다.

  • 수정된 sql

    1
    2
    3
    4
    5
    6
    7
    8
    9
    		 
    select p.product_no, p.product_title, i.image_src
    from
    (
    select row_number() over(order by a.product_no desc) as rn, a.product_no, a.product_title, a.product_user_no
    from product a
    ) as p
    left join image i on i.image_product_no = p.product_no and i.deleted = 'N'
    where rn <= 15
    order by p.product_no desc;

  • 결과

row_number 함수는 가져올 데이터의 갯수를 정하고 해당 값이 변동되지 않고서 정해진 데이터의 집합내에 모든 데이터를 가져와 주는 기능으로 사용하는 함수인듯 하다.
먼저 from절에서 서브쿼리로 데이터의 집합을 정하고 해당 집합의 데이터를 가져왔다.
이 방식을 사용하니 15개의 상품을 정상적으로 가져올수 있었고 이제 이 쿼리를 TypeOrm으로 가져가서 사용하면 될듯하다..
데이터베이스도 공부를 놓으면 안된다는걸 느끼는 과정이였다…

HOME

링크

카테고리

최근 글

태그

CodeDeploy2
Docker6
Node.js3
Nodejs15
Passport1
PostgreSQL3
Projects8
Regexp1
Ruby7
Ruby on Rails3
SQLD4
TypeORM4
aws8
aws-serverless-express2
ec27
express8
github5
javascript15
lambda2
rubymine1
sequelize3
webstorm1
×