Looker 프로젝트에서 스프레드시트 테이블을 사용자의 위치와 같이 이미 수집한 다른 사용자 데이터와 조인하는 데이터 모델을 생성합니다. SQL을 사용하여 데이터베이스에서 발생하는 이름을 정규화할 수도 있습니다. 스프레드시트에 대한 과중한 부하가 방지되도록 BigQuery에서 데이터를 캐시할 수 있습니다.
다음은 결과 LookML이 어떻게 표시되는지 보여주는 예입니다.
explore: names_sheet {
persist_for: "60 seconds"
join: names_facts {
sql_on: ${names_sheet.normalized_name} = ${names_facts.normalized_name} ;;
sql_where: ${names_facts.city} ;;
relationship: one_to_one
}
view_name: names_sheet {
derived_table: {
persist_for: "2 minutes"
sql:
SELECT row_number() OVER() as id, name, UPPER(CASE WHEN REGEXP_MATCH(name, r'\,')
THEN REGEXP_EXTRACT(name, r', (\w+)')
ELSE REGEXP_EXTRACT(name, r'^(\w+)')
END
) as normalized_name FROM namesheet.names ;;
}
dimension: id {
type: number
}
dimension: name {
order_by_field: id # keep the rows in the original order
}
dimension: normalized_name {
}
measure: count {
type: count
drill_fields: [id, name, names_facts.city]
}
measure: count_from_new_york_city {
type: count
filters: [names_facts.city: "New York City"]
}
measure: percentage_from_new_york_city {
type: number
sql: ${count_from_new_york_city}/${count} ;;
value_format_name: percent_2
}
measure: average_age_median {
type: average
sql: ${names_facts.age_median} ;;
value_format: "0000"
}
}
}
이 모델에서는 데이터를 사용하여 Explore를 만들고 Google Forms에서 수집하고 Google 시트에 입력한 모든 사용자 이름과 각 개별 사용자에 대한 기타 정보를 보여주는 Look과 대시보드를 만들 수 있습니다.
[[["이해하기 쉬움","easyToUnderstand","thumb-up"],["문제가 해결됨","solvedMyProblem","thumb-up"],["기타","otherUp","thumb-up"]],[["이해하기 어려움","hardToUnderstand","thumb-down"],["잘못된 정보 또는 샘플 코드","incorrectInformationOrSampleCode","thumb-down"],["필요한 정보/샘플이 없음","missingTheInformationSamplesINeed","thumb-down"],["번역 문제","translationIssue","thumb-down"],["기타","otherDown","thumb-down"]],["최종 업데이트: 2025-07-31(UTC)"],[],[],null,["# Live spreadsheets in databases\n\nThe BigQuery team has developed the ability to treat a Google Sheet like a table in a database. That means you can join a billion-row BigQuery table with a 100-row mapping table that you type into a spreadsheet in a single query.\n\n\nThis feature lets you configure data models, create a data pipeline for ad hoc data, or compare up-to-date numbers to your latest goals and projections.\n\nExample\n-------\n\n\nFor example, you might want to model usernames in Looker that were collected using a Google form.\n\n\nTo collect and model the information in Looker, you would perform the following steps:\n\n1. Using Google Forms, [collect the data into a Google sheet](https://support.google.com/docs/answer/2917686). Every submission adds a row in the sheet.\n2. Share the spreadsheet with the service account that you use to connect to Looker, or grant access to users with a link (view only).\n3. Make sure that the [Drive API and Sheets API are enabled for your project](https://console.developers.google.com/apis/library).\n4. Create a table from the [BigQuery interface](/bigquery) that gets its content from the spreadsheet.\n5. In your Looker project, [generate a data model](/looker/docs/generating-a-model) that joins the spreadsheet table with other user data that you might already have collected, such as users' locations. You can also use SQL to normalize the names that are coming from the database. The data can be cached in BigQuery to avoid heavy load on the spreadsheet.\n\n\nThe following is an example of what the resulting LookML might look like: \n\n```\nexplore: names_sheet {\n persist_for: \"60 seconds\"\n\n join: names_facts {\n sql_on: ${names_sheet.normalized_name} = ${names_facts.normalized_name} ;;\n sql_where: ${names_facts.city} ;;\n relationship: one_to_one\n }\n\n view_name: names_sheet {\n derived_table: {\n persist_for: \"2 minutes\"\n sql:\n SELECT row_number() OVER() as id, name, UPPER(CASE WHEN REGEXP_MATCH(name, r'\\,')\n THEN REGEXP_EXTRACT(name, r', (\\w+)')\n ELSE REGEXP_EXTRACT(name, r'^(\\w+)')\n END\n ) as normalized_name FROM namesheet.names ;;\n }\n\n dimension: id {\n type: number\n }\n\n dimension: name {\n order_by_field: id # keep the rows in the original order\n }\n\n dimension: normalized_name {\n }\n\n measure: count {\n type: count\n drill_fields: [id, name, names_facts.city]\n }\n\n measure: count_from_new_york_city {\n type: count\n filters: [names_facts.city: \"New York City\"]\n }\n\n measure: percentage_from_new_york_city {\n type: number\n sql: ${count_from_new_york_city}/${count} ;;\n value_format_name: percent_2\n }\n\n measure: average_age_median {\n type: average\n sql: ${names_facts.age_median} ;;\n value_format: \"0000\"\n }\n }\n}\n```\n\n\nFrom this model, you can create [Explores](/looker/docs/creating-and-editing-explores) with your data and build [Looks](/looker/docs/saving-and-editing-looks) and [dashboards](/looker/docs/creating-user-defined-dashboards) that show metrics about all the usernames that you've collected from Google Forms and entered into the Google Sheet, as well as other information about each individual user."]]