INFORMATION_SCHEMA.MATERIALIZED_VIEWS 뷰에는 구체화된 뷰에 대한 상태가 포함됩니다.
필수 권한
INFORMATION_SCHEMA.MATERIALIZED_VIEWS 뷰를 쿼리하는 권한을 얻으려면 관리자에게 프로젝트 또는 데이터 세트에 대한 BigQuery 메타데이터 뷰어(roles/bigquery.metadataViewer) IAM 역할을 부여해 달라고 요청하세요.
역할 부여에 대한 자세한 내용은 프로젝트, 폴더, 조직에 대한 액세스 관리를 참조하세요.
이 사전 정의된 역할에는 INFORMATION_SCHEMA.MATERIALIZED_VIEWS 뷰를 쿼리하는 데 필요한 권한이 포함되어 있습니다. 필요한 정확한 권한을 보려면 필수 권한 섹션을 펼치세요.
필수 권한
INFORMATION_SCHEMA.MATERIALIZED_VIEWS 뷰를 쿼리하려면 다음 권한이 필요합니다.
INFORMATION_SCHEMA.MATERIALIZED_VIEWS 뷰를 쿼리하면 데이터 세트에 있는 각 구체화된 뷰마다 행이 하나씩 쿼리 결과에 포함됩니다.
INFORMATION_SCHEMA.MATERIALIZED_VIEWS 뷰에는 다음과 같은 스키마가 있습니다.
열 이름
데이터 유형
값
TABLE_CATALOG
STRING
데이터 세트가 포함된 프로젝트의 이름입니다. projectId라고도 합니다.
TABLE_SCHEMA
STRING
구체화된 뷰가 포함된 데이터 세트의 이름입니다. datasetId라고도 합니다.
TABLE_NAME
STRING
구체화된 뷰의 이름입니다. tableId라고도 합니다.
LAST_REFRESH_TIME
TIMESTAMP
이 구체화된 뷰가 마지막으로 새로고침된 시간입니다.
REFRESH_WATERMARK
TIMESTAMP
구체화된 뷰의 새로고침 워터마크입니다. 이 기간까지 구체화된 뷰 기본 테이블에 포함된 데이터가 구체화된 뷰 캐시에 포함됩니다.
LAST_REFRESH_STATUS
RECORD
마지막 자동 새로고침 작업의 오류 결과를 ErrorProto 객체로 표시합니다. 있는 경우 마지막 자동 새로고침에 실패했음을 나타냅니다.
범위 및 구문
이 뷰에 대한 쿼리에는 데이터 세트나 리전 한정자가 포함되어야 합니다. 데이터 세트 한정자가 있는 쿼리에는 데이터 세트에 대한 권한이 있어야 합니다.
리전 한정자가 있는 쿼리에는 프로젝트에 대한 권한이 있어야 합니다.
자세한 내용은 구문을 참조하세요.
다음 표에서는 이 뷰의 리전 범위와 리소스 범위를 설명합니다.
DATASET_ID: 데이터 세트의 ID입니다. 자세한 내용은 데이터 세트 한정자를 참조하세요.
예를 들면 다음과 같습니다.
-- Returns metadata for views in a single dataset.SELECT*FROMmyDataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS;-- Returns metadata for all views in a region.SELECT*FROMregion-us.INFORMATION_SCHEMA.MATERIALIZED_VIEWS;
예시
예시 1:
다음 예시에서는 INFORMATION_SCHEMA.MATERIALIZED_VIEWS 뷰에서 모든 비정상 구체화된 뷰를 검색합니다. 기본 프로젝트 myproject의 mydataset에 NULLlast_refresh_status가 아닌 값이 있는 구체화된 뷰를 반환합니다.
기본 프로젝트가 아닌 프로젝트에 쿼리를 실행하려면 프로젝트 ID를 `project_id`.dataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS 형식으로 데이터 세트에 추가합니다(예: `myproject`.mydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS).
다음 예시에서는 기본 프로젝트인 myproject의 mydataset에 있는 구체화된 뷰 myview의 last_refresh_time 및 refresh_watermark를 검색합니다. 결과는 구체화된 뷰가 마지막으로 새로고침된 시점과 기본 테이블의 데이터가 구체화된 뷰 캐시로 수집될 때까지를 보여줍니다.
기본 프로젝트가 아닌 프로젝트에 쿼리를 실행하려면 프로젝트 ID를 `project_id`.dataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS 형식으로 데이터 세트에 추가합니다(예: `myproject`.mydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS).
[[["이해하기 쉬움","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-08-26(UTC)"],[[["\u003cp\u003eThe \u003ccode\u003eINFORMATION_SCHEMA.MATERIALIZED_VIEWS\u003c/code\u003e view provides status information about materialized views, including last refresh time, refresh watermark, and last refresh status.\u003c/p\u003e\n"],["\u003cp\u003eTo query the \u003ccode\u003eINFORMATION_SCHEMA.MATERIALIZED_VIEWS\u003c/code\u003e view, you need the \u003ccode\u003eBigQuery Metadata Viewer\u003c/code\u003e role or specific permissions like \u003ccode\u003ebigquery.tables.get\u003c/code\u003e and \u003ccode\u003ebigquery.tables.list\u003c/code\u003e on your project or dataset.\u003c/p\u003e\n"],["\u003cp\u003eQueries against this view require a dataset or region qualifier, and you must have the appropriate permissions for either the dataset or the project.\u003c/p\u003e\n"],["\u003cp\u003eThe view's schema includes columns like \u003ccode\u003eTABLE_CATALOG\u003c/code\u003e, \u003ccode\u003eTABLE_SCHEMA\u003c/code\u003e, \u003ccode\u003eTABLE_NAME\u003c/code\u003e, \u003ccode\u003eLAST_REFRESH_TIME\u003c/code\u003e, \u003ccode\u003eREFRESH_WATERMARK\u003c/code\u003e, and \u003ccode\u003eLAST_REFRESH_STATUS\u003c/code\u003e, offering insights into materialized view metadata.\u003c/p\u003e\n"],["\u003cp\u003eThe materialized view is under a pre-GA offering, meaning that the product or feature is available "as is" and might have limited support.\u003c/p\u003e\n"]]],[],null,["# MATERIALIZED_VIEWS view\n=======================\n\n|\n| **Preview**\n|\n|\n| This product or feature is subject to the \"Pre-GA Offerings Terms\" in the General Service Terms section\n| of the [Service Specific Terms](/terms/service-terms#1).\n|\n| Pre-GA products and features are available \"as is\" and might have limited support.\n|\n| For more information, see the\n| [launch stage descriptions](/products#product-launch-stages).\n\nThe `INFORMATION_SCHEMA.MATERIALIZED_VIEWS` view contains status about materialized views.\n\nRequired permissions\n--------------------\n\n\nTo get the permissions that\nyou need to query the `INFORMATION_SCHEMA.MATERIALIZED_VIEWS` view,\n\nask your administrator to grant you the\n\n\n[BigQuery Metadata Viewer](/iam/docs/roles-permissions/bigquery#bigquery.metadataViewer) (`roles/bigquery.metadataViewer`)\nIAM role on your project or dataset.\n\n\nFor more information about granting roles, see [Manage access to projects, folders, and organizations](/iam/docs/granting-changing-revoking-access).\n\n\nThis predefined role contains\n\nthe permissions required to query the `INFORMATION_SCHEMA.MATERIALIZED_VIEWS` view. To see the exact permissions that are\nrequired, expand the **Required permissions** section:\n\n\n#### Required permissions\n\nThe following permissions are required to query the `INFORMATION_SCHEMA.MATERIALIZED_VIEWS` view:\n\n- ` bigquery.tables.get `\n- ` bigquery.tables.list`\n\n\nYou might also be able to get\nthese permissions\nwith [custom roles](/iam/docs/creating-custom-roles) or\nother [predefined roles](/iam/docs/roles-overview#predefined).\nFor more information about BigQuery permissions, see [Access control with IAM](/bigquery/docs/access-control).\n\n\u003cbr /\u003e\n\nSchema\n------\n\nWhen you query the `INFORMATION_SCHEMA.MATERIALIZED_VIEWS` view, the query results contain\none row for each materialized view in a dataset.\n\nThe `INFORMATION_SCHEMA.MATERIALIZED_VIEWS` view has the following schema:\n\nScope and syntax\n----------------\n\nQueries against this view must include a dataset or a region qualifier. For\nqueries with a dataset qualifier, you must have permissions for the dataset.\nFor queries with a region qualifier, you must have permissions for the project.\nFor more\ninformation, see [Syntax](/bigquery/docs/information-schema-intro#syntax).\nThe following table explains the region and resource scopes for this view:\n\nReplace the following:\n\n- Optional: \u003cvar translate=\"no\"\u003ePROJECT_ID\u003c/var\u003e: the ID of your Google Cloud project. If not specified, the default project is used.\n- \u003cvar translate=\"no\"\u003eREGION\u003c/var\u003e: any [dataset region name](/bigquery/docs/locations). For example, ```region-us```.\n- \u003cvar translate=\"no\"\u003eDATASET_ID\u003c/var\u003e: the ID of your dataset. For more information, see [Dataset qualifier](/bigquery/docs/information-schema-intro#dataset_qualifier).\n\n \u003cbr /\u003e\n\n \u003cbr /\u003e\n\n | **Note:** You must use [a region qualifier](/bigquery/docs/information-schema-intro#region_qualifier) to query `INFORMATION_SCHEMA` views. The location of the query execution must match the region of the `INFORMATION_SCHEMA` view.\n\n\u003cbr /\u003e\n\nFor example: \n\n -- Returns metadata for views in a single dataset.\n SELECT * FROM myDataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS;\n\n -- Returns metadata for all views in a region.\n SELECT * FROM region-us.INFORMATION_SCHEMA.MATERIALIZED_VIEWS;\n\nExamples\n--------\n\n##### Example 1:\n\nThe following example retrieves all the unhealthy materialized views from the\n`INFORMATION_SCHEMA.MATERIALIZED_VIEWS` view. It returns the materialized views\nwith non `NULL` `last_refresh_status` values in `mydataset` in your default\nproject --- `myproject`.\n\nTo run the query against a project other than your default project, add the\nproject ID to the dataset in the following format:\n```````\u003cvar translate=\"no\"\u003eproject_id\u003c/var\u003e````.```\u003cvar translate=\"no\"\u003edataset\u003c/var\u003e```.INFORMATION_SCHEMA.MATERIALIZED_VIEWS```;\nfor example, ```myproject`.mydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS``. \n\n```googlesql\nSELECT\n table_name, last_refresh_status\nFROM\n mydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS\nWHERE\n last_refresh_status IS NOT NULL;\n```\n| **Note:** `INFORMATION_SCHEMA` view names are case-sensitive.\n\nThe result is similar to the following:\n\n\u003cbr /\u003e\n\n```\n +---------------+---------------------------------------------------------------------+\n | table_name | last_refresh_status |\n +---------------------------------------------------------------------+---------------+\n | myview | {\"reason\":\"invalidQuery\",\"location\":\"query\",\"message\":\"...\"} |\n +---------------------------------------------------------------------+---------------+\n \n```\n\n\u003cbr /\u003e\n\n##### Example 2:\n\nThe following example retrieves the `last_refresh_time` and `refresh_watermark`\nof materialized view `myview` in `mydataset` in your default project ---\n`myproject`. The result shows when the materialized was last refreshed and up to\nwhen data of base tables are collected into the materialized view cache.\n\nTo run the query against a project other than your default project, add the\nproject ID to the dataset in the following format:\n```````\u003cvar translate=\"no\"\u003eproject_id\u003c/var\u003e````.```\u003cvar translate=\"no\"\u003edataset\u003c/var\u003e```.INFORMATION_SCHEMA.MATERIALIZED_VIEWS```;\nfor example, ```myproject`.mydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS``. \n\n```googlesql\nSELECT\n table_name, last_refresh_time, refresh_watermark\nFROM\n mydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS\nWHERE\n table_name = 'myview';\n```\n| **Note:** `INFORMATION_SCHEMA` view names are case-sensitive.\n\nThe result is similar to the following:\n\n\u003cbr /\u003e\n\n```\n +---------------+------------------------------------------------+\n | table_name | last_refresh_time | refresh_watermark |\n +---------------+------------------------------------------------+\n | myview | 2023-02-22 19:37:17 | 2023-03-08 16:52:57 |\n +---------------+------------------------------------------------+\n \n```\n\n\u003cbr /\u003e\n\n| **Note:** If there have been no recent changes to the base tables, BigQuery periodically increases the `refresh_watermark` to indicate that the materialized view is up-to-date without actually refreshing it. As a result, the `last_refresh_time` can be earlier than the `refresh_watermark`."]]
