Databricks에서이 노트북 사용해보기

2018 년 11 월 10 일 업데이트

Pivot은 Apache Spark 1.6에서 사용자가 한 열의 고유 한 값을 개별 열로 변환하여 테이블 반환 식을 회전 할 수있는 새로운 DataFrame 기능으로 처음 도입되었습니다.

Apache Spark 2.4 릴리스 데이터 피벗의이 강력한 기능을 SQL 사용자에게도 확장합니다. 이 블로그에서는 시애틀의 온도 기록을 사용하여이 일반적인 SQL Pivot 기능을 사용하여 복잡한 데이터 변환을 수행하는 방법을 보여줍니다.

Pivot으로 여름 온도 검사

이번 여름 7 월 9 일 동안 시애틀의 기온이 불편한 수준으로 상승하여 최고 80 도와 90도까지 올라갔습니다.

날짜	온도 (° F)
2018 년 7 월 22 일	86
2018 년 7 월 23 일	90
07-24-2018	91
07-25-2018	92
2018 년 7 월 26 일	92
2018 년 7 월 27 일	88
2018 년 7 월 28 일	85
2018 년 7 월 29 일	94
2018 년 7 월 30 일	89

역사적인 경향이 있는지 조사하거나 조사하고 싶다고 가정 해 보겠습니다. d 상승하는 수은 수준. 이러한 수치를 조사하고 제시하는 직관적 인 방법 중 하나는 월을 열로 한 다음 각 연도의 월 평균 최고치를 단일 행에 표시하는 것입니다. 이렇게하면 서로 다른 연도의 수평, 인접 달 및 수직 온도를 쉽게 비교할 수 있습니다.

이제 PIVOT 구문을 지원합니다. Spark SQL에서는 다음 SQL 쿼리로이를 달성 할 수 있습니다.

위 쿼리는 다음과 같은 결과를 생성합니다.

음, 좋은 해와 나쁜 해가있는 것 같습니다. 2016 년은 에너지 친화적 인 해로 보입니다.

SQL 피봇 팅

이 쿼리를 자세히 살펴보고 작동 방식을 알아 보겠습니다. 먼저 피벗의 입력 인 FROM 절을 지정해야합니다. 즉, 피벗이 수행 될 기반이되는 테이블 또는 하위 쿼리를 지정해야합니다. 우리의 경우, 우리는 년, 월, 고온에 대해 염려하므로 하위 쿼리에 나타나는 필드입니다.

둘째, 쿼리의 또 다른 중요한 부분을 고려해 보겠습니다. PIVOT 절. PIVOT 절의 첫 번째 인수는 집계 함수와 집계 할 열입니다. 그런 다음 FOR 하위 절의 피벗 열을 두 번째 인수로 지정하고 피벗 열 값을 포함하는 IN 연산자를 다음과 같이 지정합니다. 마지막 인수.

피벗 열은 테이블이 회전되는 지점이며 피벗 열 값은 출력 테이블의 열로 전치됩니다. IN 절을 사용하면 각 피벗 값에 대한 별칭을 지정할 수 있으므로보다 의미있는 열 이름을 쉽게 생성 할 수 있습니다.

피벗에 대한 중요한 아이디어는 피벗 열과 함께 암시 적 group-by 열 목록을 기반으로 그룹화 된 집계를 수행합니다. 암시 적 group-by 열은 집계 함수 또는 피벗 열로 표시되지 않는 FROM 절의 열입니다.

위 쿼리에서 피벗 열이 열 월이고 암시 적 group-by 열이 열 연도 인 식 avg(temp)는 (year, month)의 각 고유 값 쌍에서 집계됩니다. 여기서 월은 지정된 피벗 열 값 중 하나와 같습니다. 결과적으로 이러한 각 집계 값은 year 행과 column 월의 해당 셀에 매핑됩니다.

이 암시 적 group-by 때문에 피벗 출력의 일부가되기를 원하지 않는 열은 FROM 절, 그렇지 않으면 쿼리가 원하지 않는 결과를 생성합니다.

여러 집계 식 지정

위의 예는 사용중인 집계 식을 하나만 보여줍니다. PIVOT 절에서 실제로 사용자는 필요한 경우 여러 집계 식을 지정할 수 있습니다. 다시 말하지만, 위의 날씨 데이터를 사용하여 6 월과 9 월 사이의 평균 최고 기온과 함께 최고 최고 기온을 나열 할 수 있습니다.

여러 집계 표현식의 경우 열은 피벗의 데카르트 곱이됩니다. 이름이 <value>_<aggExpr> 인 열 값 및 집계 표현식.

열 그룹화 대 피벗 열

이제 일별 저온 표에서 온도 추세 탐색에 저온을 포함한다고 가정합니다.

날짜	온도 (° F)
…	…
2018 년 8 월 1 일	59
2018 년 8 월 2 일	58
2018 년 3 월 8 일	59
2018 년 8 월 4 일	58
2018 년 8 월 5 일	59
2018 년 8 월 6 일	59
…	…

이 테이블을 이전의 일일 최고 기온 테이블과 결합하기 위해 “Date”열에서이 두 테이블을 결합 할 수 있습니다. 그러나 날짜를 그룹화하는 피벗을 사용할 것이므로 UNION ALL. 나중에 보게 되겠지만이 접근 방식은 더 많은 유연성을 제공합니다.

이제 새로 결합 된 테이블로 피벗 쿼리를 시도해 보겠습니다.

결과적으로 하나의 표에서 지난 4 년 동안 매월 평균 최고 및 최저를 얻습니다. 피벗 쿼리에 flag 열을 포함해야합니다. 그렇지 않으면 avg(temp) 표현식이 높음과 낮음의 혼합을 기반으로합니다. 기온.

이제 매년 두 개의 행이 있는데, 하나는 고온 용이고 다른 하나는 저온 용입니다. 피벗 입력에 flag 열이 하나 더 포함 되었기 때문입니다.이 열은 원래 열 year.

또는 그룹화 열 대신 flag를 피벗 열로 사용할 수도 있습니다. 이제 두 개의 피벗 열, month 및 flag가 있습니다.

이 쿼리는 다음과 같은 다른 레이아웃을 제공합니다. 동일한 데이터, 매년 1 행이지만 매달 2 열.

다음 단계

이 블로그에 사용 된 쿼리 예제를 실행하려면 피벗 SQL 예제를 확인하십시오. 함께 제공되는 노트북에서.

공헌 해 주신 Apache Spark 커뮤니티 기여자에게 감사드립니다!