pgloder 자동 생성 스크립

[생짜초보]

#!/bin/bash

# PostgreSQL 연결 정보 설정
PG_HOST="localhost"
PG_PORT="5456"
PG_PASSWORD="test"
PG_USER="test"
PG_DATABASE="scottdb"

# 사용자 입력 및 변수 설정
echo "Enter PostgreSQL connection URL (e.g., postgresql://test@localhost:5456/scottdb):"
site_url=" postgresql://test@localhost:5456/scottdb"

echo "Connection URL: $site_url"

echo "Enter the table name to load data into:"
table_name="dept"

data_file="/var/lib/pgsql/pgloader/$table_name.dat"

# 데이터 파일 내용 읽어오기
dept_data=$(cat $data_file)

# 컬럼 정보 및 데이터 길이 계산
column_info=$(PGPASSWORD=$PG_PASSWORD psql -U $PG_USER -d $PG_DATABASE -h $PG_HOST  -p $PG_PORT -c "\\copy ( SELECT column_name, data_type FROM information_schema.columns WHERE table_name='$table_name' ORDER BY ordinal_position) TO STDOUT WITH DELIMITER ',' CSV")
echo "log1컬럼정보:::::::::$column_info"

# 데이터 처리 및 LOAD 문 생성
# handle_data_type 함수 정의
function handle_data_type {


local data_type="$1"
echo "ininininidata_type:::::::::$data_type::::::::::::::::::$table_name::::::::::::::::::$column_name" 

 case "$data_type" in
    "numeric")
   echo "Calculated length============================="
        data_length=5
      echo "Calculated length $data_length"
  return 0
      ;;
   
    "character varying")
      # 문자형 데이터 처리 로직 추가
      echo "**INFO:** 문자형 데이터 처리..."
  echo "Calculated length============================="
        data_length=5
      echo "Calculated length $data_length"
      return 1
      ;;
    "date")
      # 날짜형 데이터 처리 로직 추가
      echo "**INFO:** 날짜형 데이터 처리..."
      return 2
      ;;
    "timestamp")
      # 타임스탬프형 데이터 처리 로직 추가
      echo "**INFO:** 타임스탬프형 데이터 처리..."
      return 3
      ;;
    "character")
      # 문자형 데이터 처리 로직 추가
      echo "**INFO:** 문자형 데이터 처리..."
      return 4
      ;;
    *)
      echo "**ERROR:** 유효하지 않은 데이터 유형: '$data_type'" >&2
      return 1
      ;;
  esac
}
function handle_data_type2 {

local data_type="$1"
 
data_column=$column_name 
echo "data_column "$data_column
}
# LOAD 문 필드 정의 및 문 완성
load_statement="LOAD FIXED FROM stdin ("
field_definitions=""
field_definitions1=""



for column in $column_info; do

 echo "log5555 column_info ===========$column_info"
  

 
 data_type=$(echo "$column_info" | awk -F ',' '{print $2}')
 column_name=$(echo "$column_info" | awk -F ',' '{print $1}')
 
echo "lastdata_type ::::::::::: $data_type::::::$column_name:::::$table_name"
 field_definitions+=$(handle_data_type $data_type  $column_name $table_name)
done

echo -e "LOAD FI:XED      
     FROM stdin  
             (                      
              field_definitions+=$(handle_data_type $data_type "\t" $column_name "\t" $table_name)
               )
 INTO postgresql://test@localhost:5456/scottdb?$table_name
       (
    field_definitions1+=$(handle_data_type2 "\t" $column_name "\t"$table_name)
        )
        WITH truncate
ET work_mem to '32 MB', maintenance_work_mem to '64 MB';"