연결된 시트

연결된 시트를 사용하면 Sheets 내에서 페타바이트 규모의 데이터를 바로 분석할 수 있습니다. 스프레드시트를 BigQuery 데이터 웨어하우스와 연결하고 피벗 테이블, 차트, 수식과 같은 익숙한 Sheets 도구를 사용하여 분석을 수행할 수 있습니다.

이 가이드에서는 공개 데이터 세트 shakespeare를 사용하여 '연결된 시트'를 사용하는 방법을 보여줍니다. 데이터 세트에는 다음 정보가 포함됩니다.

필드 유형 설명
word 문자열 코퍼스에서 추출된 단일 고유 단어 (공백이 구분자임)입니다.
word_count INTEGER 이 코퍼스에서 이 단어가 등장하는 횟수입니다.
corpus 문자열 이 단어가 추출된 저작물입니다.
corpus_date INTEGER 이 코퍼스가 게시된 연도입니다.

데이터 소스로 작업하기

BigQuery 데이터 소스 추가

데이터 소스를 추가하려면 batchUpdate 메서드에 AddDataSourceRequest를 제공합니다. 요청 본문은 DataSource 필드를 지정해야 합니다.

아래의 <YOUR_PROJECT_ID>를 유효한 Google Cloud 프로젝트 ID로 바꿉니다.

"addDataSource":{
   "dataSource":{
      "spec":{
         "bigQuery":{
            "projectId":"<YOUR_PROJECT_ID>",
            "tableSpec":{
               "tableProjectId":"bigquery-public-data",
               "datasetId":"samples",
               "tableId":"shakespeare"
            }
         }
      }
   }
}

데이터 소스가 만들어지면 연결된 DATA_SOURCE 시트가 생성되어 최대 500개 행의 미리보기를 제공합니다. 미리보기는 즉시 사용할 수 없습니다. BigQuery 데이터를 가져오기 위해 비동기적으로 실행이 트리거됩니다.

AddDataSourceResponse에는 다음 필드가 포함되어 있습니다.

  • dataSource: 새로 만든 DataSource. dataSourceId도 채워지며, 데이터 소스에서 각 DataSource 객체를 만들기 위해 참조됩니다.

  • dataExecutionStatus: 위에서 언급했듯이 BigQuery 데이터를 미리보기 시트로 가져오는 실행 상태입니다. 자세한 내용은 DataExecutionStatus를 참조하세요.

데이터 소스 업데이트 또는 삭제

batchUpdate 메서드를 사용하고 이에 따라 UpdateDataSourceRequest 또는 DeleteDataSourceRequest 요청을 제공합니다.

데이터 소스 객체 작업

데이터 소스가 스프레드시트에 추가되면 데이터 소스에서 데이터 소스 객체를 만들 수 있습니다. 데이터 소스 객체는 피벗 테이블, 차트, 수식과 같은 일반적인 Sheets 도구이지만 연결된 시트와 통합되어 분석을 지원합니다.

객체에는 네 가지 유형이 있습니다.

  • 데이터 소스 테이블
  • 데이터 소스 피벗 테이블
  • 데이터 소스 차트
  • DataSource 수식

DataSource 테이블 추가

Sheets 편집기에서 '추출'이라고도 하는 이 객체는 데이터 소스에서 Sheets로 데이터의 정적 덤프를 가져옵니다. 피벗 테이블과 마찬가지로 표가 지정되고 왼쪽 상단 셀에 고정됩니다.

이 예에서는 batchUpdate 메서드를 사용하고 updateCells 요청을 제공하여 wordword_count 열 2개(최대 1,000행)로 구성된 데이터 소스 테이블을 만듭니다.

"updateCells":{
   "rows":{
      "values":[
         {
            "dataSourceTable":{
               "dataSourceId":"<YOUR_DATA_SOURCE_ID>",
               "columns":[
                  {
                     "name":"word"
                  },
                  {
                     "name":"word_count"
                  }
               ],
               "rowLimit":{
                  "value":1000
               },
               "columnSelectionType":"SELECTED"
            }
         }
      ]
   },
   "fields":"dataSourceTable"
}

데이터 소스 테이블이 성공적으로 생성된 후에는 데이터를 즉시 사용할 수 없습니다. 스프레드시트 편집기에는 미리보기로 표시됩니다. BigQuery 데이터를 가져오려면 데이터 소스 테이블을 새로고침해야 합니다. 동일한 batchUpdate 내에서 RefreshDataSourceRequest를 지정할 수 있습니다. 자세한 내용은 아래의 DataSource 객체 새로고침을 참고하세요. 모든 데이터 소스 객체는 비슷한 방식으로 작동합니다.

새로고침이 완료되면 (BigQuery 데이터를 가져옴) 데이터 소스 테이블이 다음과 같이 채워집니다.

셰익스피어 데이터 세트의 데이터를 보여주는 데이터 소스 테이블의 스크린샷

DataSource 피벗 테이블 추가

기존의 피벗 테이블과 달리 데이터 소스 피벗 테이블은 데이터 소스로 지원되며 열 이름으로 데이터를 참조합니다. 이 예에서는 코퍼스별 총 단어 수를 보여주는 피벗 테이블을 만듭니다.

"updateCells":{
   "rows":{
      "values":[
         {
            "pivotTable":{
               "dataSourceId":"<YOUR_DATA_SOURCE_ID>",
               "rows":{
                  "dataSourceColumnReference":{
                     "name":"corpus"
                  },
                  "sortOrder":"ASCENDING"
               },
               "values":{
                  "summarizeFunction":"SUM",
                  "dataSourceColumnReference":{
                     "name":"word_count"
                  }
               }
            }
         }
      ]
   },
   "fields":"pivotTable"
}

BigQuery 데이터를 가져오면 데이터 소스 피벗 테이블이 다음과 같이 채워집니다.

셰익스피어 데이터 세트의 데이터를 보여주는 데이터 소스 피벗 스크린샷

데이터 소스 차트 추가

이 예에서는 COLUMN 유형의 데이터 소스 차트를 만들어 코퍼스별 총 단어 수를 표시합니다.

"addChart":{
   "chart":{
      "spec":{
         "title":"Corpus by word count",
         "basicChart":{
            "chartType":"COLUMN",
            "domains":[
               {
                  "domain":{
                     "columnReference":{
                        "name":"corpus"
                     }
                  }
               }
            ],
            "series":[
               {
                  "series":{
                     "columnReference":{
                        "name":"word_count"
                     },
                     "aggregateType":"SUM"
                  }
               }
            ]
         }
      },
      "dataSourceChartProperties":{
         "dataSourceId":"<YOUR_DATA_SOURCE_ID>"
      }
   }
}

BigQuery 데이터를 가져오면 데이터 소스 차트가 다음과 같이 렌더링됩니다.

셰익스피어 데이터 세트의 데이터를 보여주는 데이터 소스 차트의 스크린샷

DataSource 수식 추가

이 예에서는 평균 단어 수를 계산하는 데이터 소스 수식을 만듭니다.

"updateCells":{
   "rows":[
      {
         "values":[
            {
               "userEnteredValue":{
                  "formulaValue":"=AVERAGE(shakespeare!word_count)"
               }
            }
         ]
      }
   ],
   "fields":"userEnteredValue"
}

BigQuery 데이터를 가져오면 데이터 소스 수식이 다음과 같이 채워집니다.

셰익스피어 데이터 세트의 데이터를 보여주는 데이터 소스 수식의 스크린샷

DataSource 객체 새로고침

데이터 소스 객체를 새로고침하여 현재 데이터 소스 사양 및 객체 구성에 따라 BigQuery에서 최신 데이터를 가져올 수 있습니다. batchUpdate 메서드를 사용하고 RefreshDataSourceRequest를 제공하고 DataSourceObjectReferences를 통해 새로고침할 객체를 하나 이상 지정할 수 있습니다.

하나의 batchUpdate 요청 내에서 데이터 소스 객체를 만들고 새로고침할 수 있습니다.

DataExecutionStatus

새 데이터 소스를 만들거나 데이터 소스 객체를 새로고침하면 BigQuery에서 데이터를 가져오고 실행 상태가 포함된 응답을 반환하도록 백그라운드에서 실행이 생성됩니다. 실행이 성공적으로 시작되면 실행 상태는 일반적으로 RUNNING 상태입니다.

프로세스가 비동기식이므로 애플리케이션은 상태가 SUCCEEDED 또는 FAILED 상태를 저장할 때까지 spreadsheets.get을 사용하여 주기적으로 데이터 소스 객체의 상태를 검색하는 폴링 모델을 구현해야 합니다. 대부분의 경우 실행이 빠르게 완료되지만 데이터 소스 사양의 복잡성에 따라 다릅니다. 어떤 경우든 실행은 10분 이상 걸리지 않습니다.