redash - datasource 생성 및 데이터 시각화 예

redash 설치후 datasource 를 생성해 봅니다.

생성 전에 data가 들어 있는 db가 필요 합니다.

저는 postgresql 을 설치해 놓아서 그걸 사용하도록 하겠습니다.

오른쪽 상단에 메뉴를 펼치면 Data Source 메뉴를 확인 할수 있습니다.

Data Source 메뉴를 클릭하면 설정창으로 이동합니다.

New Data Source를 클릭해줍니다.

팝업창이 뜨면 저는 postgresql 을 연결해야 하니 postgresql 을 찾아 줍니다.

해당 db 정보를 입력하고 create 버튼을 누릅니다.

db 정보를 제대로 입력하고 Test Connection 버튼을 클릭하면 오른쪽 하단에 Success 메시지가 나타납니다.

DataSource가 생성 되었으니 Query를 생성해 봅니다.

메뉴에서 Creat 아래 Query를 선택합니다.

테스트로 사용한 데이터는 공공 데이터 포털에 있는 :

서울교통공사_지하철혼잡도 (https://www.data.go.kr/data/15071311/fileData.do)

서울교통공사_1_8호선 역사 좌표(위경도) 정보(https://www.data.go.kr/data/15099316/fileData.do)

csv 형식에 맞게 table을 만들어 줍니다.

db tool 은 dbeaver를 사용해서 진행했습니다.

/* 혼잡도 */
CREATE table subway.subway_traff (
    연번 int4 NULL,
    조사일자 varchar(50) NULL,
    호선 int4 NULL,
    역번호 int4 NULL,
    역명 varchar(50) NULL,
    구분 varchar(50) NULL,
    "5시30분" float4 NULL,
    "6시00분" float4 NULL,
    "6시30분" float4 NULL,
    "7시00분" float4 NULL,
    "7시30분" float4 NULL,
    "8시00분" float4 NULL,
    "8시30분" float4 NULL,
    "9시00분" float4 NULL,
    "9시30분" int4 NULL,
    "10시00분" float4 NULL,
    "10시30분" float4 NULL,
    "11시00분" float4 NULL,
    "11시30분" float4 NULL,
    "12시00분" float4 NULL,
    "12시30분" float4 NULL,
    "13시00분" float4 NULL,
    "13시30분" float4 NULL,
    "14시00분" float4 NULL,
    "14시30분" float4 NULL,
    "15시00분" float4 NULL,
    "15시30분" float4 NULL,
    "16시00분" float4 NULL,
    "16시30분" float4 NULL,
    "17시00분" float4 NULL,
    "17시30분" float4 NULL,
    "18시00분" float4 NULL,
    "18시30분" float4 NULL,
    "19시00분" int4 NULL,
    "19시30분" float4 NULL,
    "20시00분" float4 NULL,
    "20시30분" int4 NULL,
    "21시00분" float4 NULL,
    "21시30분" float4 NULL,
    "22시00분" float4 NULL,
    "22시30분" float4 NULL,
    "23시00분" float4 NULL,
    "23시30분" float4 NULL
);

/* 역정보 */
CREATE TABLE subway.subway_info (
	연번 int4 NULL,
	호선 int4 NULL,
	역번호 int4 NULL,
	역명 varchar(50) NULL,
	경도 float4 NULL,
	위도 float4 NULL,
	데이터기준일자 varchar(50) NULL
);

생성된 테이블에서 오른쪽키를 눌러서 데이터 가져오기 클릭

내려받은 csv파일을 선택해 주고 다음 버튼 클릭

다음 다음 눌러서 왼쪽의 confirm 단계 까지 오면

진행(P) 버튼을 클릭

작업이 끝나면 아래와 같이 데이터가 적재된 것을 확인 할수 있습니다.

만약에 인코딩 오류가 나면 공공데이터 포털에서 내려받은 csv의 인코딩을 utf-8로 변경해서 저장 줍니다.

테스트해볼 데이터가 적재 되었으면 이제 redash 에서 불러와 봅니다.

메뉴에서 Create>Query를 클릭합니다.

sql을 작성하고 시각화 해 봅니다. 아래 sql을 실행 하였습니다. (시간대는 원하시는 시간대로 하시면 됩니다.)

select  t1.역번호
      , t1.역명
      , t1.역명 as 역명2
      , t1.조사일자 
      , t1.호선 
      , t1.구분 
      , t1.time_div
      , avg(t1.val) as val
      , max(t1.경도) as 경도
      , max(t1.위도) as 위도
from ( 
select a.역번호
      , a.역명
      , a.역명 as 역명2
      , a.조사일자 
      , a.호선 
      , a.구분 
      , case 
             when num = 1 then '7시30분'
             when num = 2 then '8시00분'
             when num = 3 then '8시30분'
             when num = 4 then '9시00분'
             when num = 5 then '17시00분'
             when num = 6 then '17시30분'
             when num = 7 then '18시00분'
             when num = 8 then '18시30분'
             when num = 9 then '19시00분'
        else null     
        end as time_div
      , case  
             when num = 1 then a."7시30분"
             when num = 2 then a."8시00분"
             when num = 3 then a."8시30분"
             when num = 4 then a."9시00분"             
             when num = 5 then a."17시00분"
             when num = 6 then a."17시30분"
             when num = 7 then a."18시00분"
             when num = 8 then a."18시30분"
             when num = 9 then a."19시00분"             
        end as val
      , b.경도 
      , b.위도 
from subway.subway_traff a
   , subway.subway_info b
   , (select generate_series(1, 9) as num ) c
where 1=1
and a.역번호 = b.역번호   
) t1 
where time_div is not null
group by t1.역번호, t1.역명, t1.역명, t1.조사일자, t1.호선, t1.구분 , t1.time_div
;

데이터가 확인 되었으면