CREATE EXTERNAL TABLE(Transact-SQL)CREATE EXTERNAL TABLE (Transact-SQL)

적용 대상: 예SQL Server(2016부터) 예Azure SQL Database 예Azure SQL Data Warehouse 예병렬 데이터 웨어하우스 APPLIES TO: yesSQL Server (starting with 2016) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

PolyBase 또는 Elastic Database 쿼리에 대한 외부 테이블을 만듭니다.Creates an external table for PolyBase, or Elastic Database queries. 시나리오에 따라 구문은 크게 다릅니다.Depending on the scenario, the syntax differs significantly. PolyBase용으로 만든 외부 테이블은 Elastic Database 쿼리에 사용할 수 없습니다.An external table created for PolyBase cannot be used for Elastic Database queries. 마찬가지로 Elastic Database 쿼리용으로 만든 외부 테이블은 PolyBase 등에 사용할 수 없습니다.Similarly, an external table created for Elastic Database queries cannot be used for PolyBase, etc.

참고

PolyBase는 SQL Server 2016(또는 그 이상), Azure SQL Data Warehouse 및 병렬 데이터 웨어하우스에서만 지원됩니다.PolyBase is supported only on SQL Server 2016 (or higher), Azure SQL Data Warehouse, and Parallel Data Warehouse. Elastic Database 쿼리는 Azure SQL Database v12 이상에서만 지원됩니다.Elastic Database queries are supported only on Azure SQL Database v12 or later.

  • SQL ServerSQL Server에서는 외부 테이블을 사용하여 Hadoop 클러스터나 Azure Blob 저장소에 저장된 데이터를 참조하는 Hadoop 클러스터나 Azure Blob 저장소 PolyBase 외부 테이블에 저장된 데이터에 액세스합니다.uses external tables to access data stored in a Hadoop cluster or Azure blob storagea PolyBase external table that references data stored in a Hadoop cluster or Azure blob storage. Elastic Database 쿼리에 대한 외부 테이블을 만드는 데에도 사용할 수 있습니다.Can also be used to create an external table for Elastic Database query.

    외부 테이블을 사용하여 다음을 수행합니다.Use an external table to:

  • Transact-SQLTransact-SQL 문을 통해 Hadoop 또는 Azure Blob Storage 데이터를 쿼리합니다.Query Hadoop or Azure blob storage data with Transact-SQLTransact-SQL statements.

  • Hadoop 또는 Azure Blob Storage의 데이터를 가져와서 SQL ServerSQL Server 데이터베이스에 저장합니다.Import and store data from Hadoop or Azure blob storage into your SQL ServerSQL Server database.

  • Elastic Database에 사용할 외부 테이블 Create an external table for use with an Elastic Database
    쿼리를 만듭니다.query.

  • Azure Data Lake Store의 데이터를 가져와서 Azure SQL Data Warehouse에 저장합니다.Import and store data from Azure Data Lake Store into Azure SQL Data Warehouse

    CREATE EXTERNAL DATA SOURCE (Transact-SQL)DROP EXTERNAL TABLE (Transact-SQL)을 참조하세요.See also CREATE EXTERNAL DATA SOURCE (Transact-SQL) and DROP EXTERNAL TABLE (Transact-SQL).

    항목 링크 아이콘 Transact-SQL 구문 규칙Topic link icon Transact-SQL Syntax Conventions

구문Syntax

-- Syntax for SQL Server 

-- Create a new external table  
CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name   
    ( <column_definition> [ ,...n ] )  
    WITH (   
        LOCATION = 'folder_or_filepath',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name  
        [ , <reject_options> [ ,...n ] ]  
    )  
[;]  

<reject_options> ::=  
{  
    | REJECT_TYPE = value | percentage  
    | REJECT_VALUE = reject_value  
    | REJECT_SAMPLE_VALUE = reject_sample_value  

}  

-- Create a table for use with Elastic Database query  
CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name   
    ( <column_definition> [ ,...n ] )  
    WITH ( <sharded_external_table_options> )  
[;]  

<sharded_external_table_options> ::=  
        DATA_SOURCE = external_data_source_name,   
        SCHEMA_NAME = N'nonescaped_schema_name',  
        OBJECT_NAME = N'nonescaped_object_name',  
        [DISTRIBUTION  = SHARDED(sharding_column_name) | REPLICATED | ROUND_ROBIN]]  
    )  
[;]  
-- Syntax for Azure SQL Database

-- Create a table for use with Elastic Database query  
CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name   
    ( <column_definition> [ ,...n ] )  
    WITH ( <sharded_external_table_options> )  
[;]  

<sharded_external_table_options> ::=  
        DATA_SOURCE = external_data_source_name,   
        SCHEMA_NAME = N'nonescaped_schema_name',  
        OBJECT_NAME = N'nonescaped_object_name',  
        [DISTRIBUTION  = SHARDED(sharding_column_name) | REPLICATED | ROUND_ROBIN]]  
    )  
[;]  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  

-- Create a new external table in SQL Server PDW  
CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name   
    ( <column_definition> [ ,...n ] )  
    WITH (   
        LOCATION = 'hdfs_folder_or_filepath',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name  
        [ , <reject_options> [ ,...n ] ]  
    )  
[;]  

<reject_options> ::=  
{  
    | REJECT_TYPE = value | percentage,  
    | REJECT_VALUE = reject_value,  
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '\REJECT_Directory'

}  

인수Arguments

database_name .database_name . [ schema_name ] .[ schema_name ] . | schema_name.| schema_name. ] table_name] table_name
만들려는 테이블의 한 부분에서 세 부분으로 이루어진 이름입니다.The one to three-part name of the table to create. 외부 테이블의 경우 테이블 메타데이터만이 Hadoop 또는 Azure Blob Storage에 참조된 파일 또는 폴더에 관한 기본 통계와 함께 SQL에 저장됩니다.For an external table, only the table metadata is stored in SQL along with basic statistics about the file and or folder referenced in Hadoop or Azure blob storage. 실제 데이터가 이동되거나 SQL ServerSQL Server에 저장되지 않습니다.No actual data is moved or stored in SQL ServerSQL Server.

<column_definition> [ ,...n ] CREATE EXTERNAL TABLE을 통해 하나 이상의 열 정의를 사용할 수 있습니다.<column_definition> [ ,...n ] CREATE EXTERNAL TABLE allows one or more column definitions. CREATE EXTERNAL TABLE과 CREATE TABLE 모두 열을 정의하는 데 같은 구문을 사용합니다.Both CREATE EXTERNAL TABLE and CREATE TABLE use the same syntax for defining a column. 이에 대한 예외는 외부 테이블에 DEFAULT CONSTRAINT를 사용할 수 없다는 것입니다.An exception to this, you cannot use the DEFAULT CONSTRAINT on external tables. 열 정의 및 해당 데이터 형식에 대한 자세한 내용은 CREATE TABLE (Transact-SQL)Azure SQL Database에 대한 CREATE TABLE을 참조하세요.For the full details about column definitions and their data types, see CREATE TABLE (Transact-SQL) and CREATE TABLE on Azure SQL Database.

데이터 형식 및 열 수를 포함한 열 정의는 외부 파일의 데이터와 일치해야 합니다.The column definitions, including the data types and number of columns, must match the data in the external files. 불일치가 있는 경우 실제 데이터를 쿼리할 때 파일 행이 거부됩니다.If there is a mismatch, the file rows will be rejected when querying the actual data.

LOCATION = 'folder_or_filepath'LOCATION = 'folder_or_filepath'
Hadoop 또는 Azure Blob Storage의 실제 데이터에 대한 폴더 또는 파일 경로 및 파일 이름을 지정합니다.Specifies the folder or the file path and file name for the actual data in Hadoop or Azure blob storage. 위치는 루트 폴더에서 시작하며, 루트 폴더는 외부 데이터 원본에 지정된 데이터 위치입니다.The location starts from the root folder; the root folder is the data location specified in the external data source.

SQL Server에서는 CREATE EXTERNAL TABLE 문이 경로 및 폴더가 없으면 만듭니다.In SQL Server, the CREATE EXTERNAL TABLE statement creates the path and folder if it does not already exist. 그런 다음, INSERT INTO를 사용하여 로컬 SQL Server 테이블에서 외부 데이터 원본으로 데이터를 내보냅니다.You can then use INSERT INTO to export data from a local SQL Server table to the external data source. 자세한 내용은 PolyBase 쿼리를 참조하세요.For more information, see PolyBase Queries.

SQL Data Warehouse 및 Analytics Platform System에서는 CREATE EXTERNAL TABLE AS SELECT 문이 존재하지 않는 경로 및 폴더를 만듭니다.In SQL Data Warehouse and Analytics Platform System, the CREATE EXTERNAL TABLE AS SELECT statement creates the path and folder if it does not exist. 이 두 제품에서는 CREATE EXTERNAL TABLE이 경로와 폴더를 만들지 않습니다.In these two products, CREATE EXTERNAL TABLE does not create the path and folder.

위치(LOCATION)를 폴더로 지정할 경우, 외부 테이블에서 선택하는 PolyBase 쿼리는 폴더 및 해당 폴더의 모든 하위 폴더에서 파일을 검색합니다.If you specify LOCATION to be a folder, a PolyBase query that selects from the external table will retrieve files from the folder and all of its subfolders. Hadoop과 마찬가지로 PolyBase도 숨겨진 폴더를 반환하지 않습니다.Just like Hadoop, PolyBase does not return hidden folders. 또한 파일 이름이 밑줄() 또는 마침표(.)로 시작하는 파일을 반환하지 않습니다.It also does not return files for which the file name begins with an underline () or a period (.).

이 예제에서 LOCATION='/webdata/'이면 PolyBase 쿼리는 mydata.txt 및 mydata2.txt에서 행을 반환합니다.In this example, if LOCATION='/webdata/', a PolyBase query will return rows from mydata.txt and mydata2.txt. mydata3.txt는 숨겨진 폴더의 하위 폴더이므로 반환하지 않습니다.It will not return mydata3.txt because it is a subfolder of a hidden folder. _hidden.txt는 숨겨진 파일이므로 반환하지 않습니다.It will not return _hidden.txt because it is a hidden file.

외부 테이블에 대한 재귀적 데이터Recursive data for external tables

기본값을 변경하고 루트 폴더에서 읽기만 하려면 core-site.xml 구성 파일에서 특성 <polybase.recursive.traversal>을 'false'로 설정합니다.To change the default and only read from the root folder, set the attribute <polybase.recursive.traversal> to 'false' in the core-site.xml configuration file. 이 파일은 <SqlBinRoot>\PolyBase\Hadoop\Conf with SqlBinRoot the bin root of SQl Server 아래에 있습니다.This file is located under <SqlBinRoot>\PolyBase\Hadoop\Conf with SqlBinRoot the bin root of SQl Server. C:\\Program Files\\Microsoft SQL Server\\MSSQL13.XD14\\MSSQL\\Binn)을 입력합니다.For example, C:\\Program Files\\Microsoft SQL Server\\MSSQL13.XD14\\MSSQL\\Binn.

DATA_SOURCE = external_data_source_nameDATA_SOURCE = external_data_source_name
외부 데이터의 위치를 포함하는 외부 데이터 원본의 이름을 지정합니다.Specifies the name of the external data source that contains the location of the external data. 이 위치는 Hadoop 또는 Azure Blob Storage입니다.This location is either a Hadoop or Azure blob storage. 외부 데이터 원본을 만들려면 CREATE EXTERNAL DATA SOURCE (Transact-SQL)을 사용합니다.To create an external data source, use CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_nameFILE_FORMAT = external_file_format_name
외부 데이터에 대한 파일 형식 및 압축 방법을 저장하는 외부 파일 형식 개체의 이름을 지정합니다.Specifies the name of the external file format object that stores the file type and compression method for the external data. 외부 파일 형식을 만들려면 CREATE EXTERNAL FILE FORMAT (Transact-SQL)을 사용합니다.To create an external file format, use CREATE EXTERNAL FILE FORMAT (Transact-SQL).

거부 옵션Reject Options
PolyBase가 외부 데이터 원본에서 검색하는 더티 레코드를 처리하는 방법을 결정하는 거부 매개 변수를 지정할 수 있습니다.You can specify reject parameters that determine how PolyBase will handle dirty records it retrieves from the external data source. 데이터 레코드는 실제 데이터 형식 또는 열 수가 외부 테이블의 열 정의와 일치하지 않으면 '더티'로 간주됩니다.A data record is considered 'dirty' if it actual data types or the number of columns do not match the column definitions of the external table.

거부 값을 지정하거나 변경하지 않으면 PolyBase는 기본값을 사용합니다.When you do not specify or change reject values, PolyBase uses default values. 거부 매개 변수에 관한 이 정보는 CREATE EXTERNAL TABLE 문을 사용하여 외부 테이블을 만들 때 추가 메타데이터로 저장됩니다.This information about the reject parameters is stored as additional metadata when you create an external table with CREATE EXTERNAL TABLE statement. 후속 SELECT 문 또는 SELECT INTO SELECT 문이 외부 데이터에서 데이터를 선택하는 경우, PolyBase는 거부 옵션을 사용하여 실제 쿼리가 실패할 때까지 거부될 수 있는 행의 개수 또는 비율을 결정합니다.When a future SELECT statement or SELECT INTO SELECT statement selects data from the external table , PolyBase will use the reject options to determine the number or percentage of rows that can be rejected before the actual query fails. 의 인스턴스에 액세스할 때마다 SQL Server 로그인을 제공할 필요가 없습니다.. 이 쿼리는 거부된 임계값이 초과될 때까지 (부분) 결과를 반환하며, 그 후 해당 오류 메시지가 발생하면 실패합니다.The query will return (partial) results until the reject threshold is exceeded; it then fails with the appropriate error message.

REJECT_TYPE = value | percentageREJECT_TYPE = value | percentage
REJECT_VALUE 옵션이 리터럴 값으로 지정되는지 또는 백분율로 지정되는지 구체화합니다.Clarifies whether the REJECT_VALUE option is specified as a literal value or a percentage.

valuevalue
REJECT_VALUE는 백분율이 아닌 리터럴 값입니다.REJECT_VALUE is a literal value, not a percentage. PolyBase 쿼리는 거부된 행 수가 reject_value를 초과하면 실패합니다.The PolyBase query will fail when the number of rejected rows exceeds reject_value.

예를 들어 REJECT_VALUE = 5이고 REJECT_TYPE = value인 경우, PolyBase SELECT 쿼리는 행 5개가 거부된 후 실패합니다.For example, if REJECT_VALUE = 5 and REJECT_TYPE = value, the PolyBase SELECT query will fail after 5 rows have been rejected.

percentagepercentage
REJECT_VALUE는 리터럴 값이 아닌 백분율입니다.REJECT_VALUE is a percentage, not a literal value. PolyBase 쿼리는 실패한 행의 백분율(percentage)이 reject_value를 초과하면 실패합니다.A PolyBase query will fail when the percentage of failed rows exceeds reject_value. 실패한 행의 백분율은 일정 간격으로 계산됩니다.The percentage of failed rows is calculated at intervals.

REJECT_VALUE = reject_valueREJECT_VALUE = reject_value
쿼리가 실패할 때까지 거부될 수 있는 값 또는 행의 백분율을 지정합니다.Specifies the value or the percentage of rows that can be rejected before the query fails.

REJECT_TYPE = value인 경우, reject_value는 0에서 2,147,483,647 사이의 정수여야 합니다.For REJECT_TYPE = value, reject_value must be an integer between 0 and 2,147,483,647.

REJECT_TYPE = percentage인 경우, reject_value는 0에서 100 사이의 부동 소수점 수이어야 합니다.For REJECT_TYPE = percentage, reject_value must be a float between 0 and 100.

REJECT_SAMPLE_VALUE = reject_sample_valueREJECT_SAMPLE_VALUE = reject_sample_value
이 특성은 REJECT_TYPE = percentage를 지정한 경우에 필요하며This attribute is required when you specify REJECT_TYPE = percentage. PolyBase가 거부된 행의 백분율을 다시 계산하기 전까지 검색을 시도하는 행 수를 결정합니다.It determines the number of rows to attempt to retrieve before the PolyBase recalculates the percentage of rejected rows.

reject_sample_value 매개 변수는 0에서 2,147,483,647 사이의 정수여야 합니다.The reject_sample_value parameter must be an integer between 0 and 2,147,483,647.

예를 들어 REJECT_SAMPLE_VALUE = 1000인 경우, PolyBase는 외부 데이터 파일에서 행 1000개 가져오기를 시도한 후 실패한 행의 백분율을 계산합니다.For example, if REJECT_SAMPLE_VALUE = 1000, PolyBase will calculate the percentage of failed rows after it has attempted to import 1000 rows from the external data file. 실패한 행의 백분율이 reject_value보다 작은 경우, PolyBase는 또 다른 행 1000개의 검색을 시도합니다.If the percentage of failed rows is less than reject_value, PolyBase will attempt to retrieve another 1000 rows. 계속해서 추가로 행 1000개의 가져오기를 시도한 후마다 실패한 행의 백분율을 다시 계산합니다.It continues to recalculate the percentage of failed rows after it attempts to import each additional 1000 rows.

참고

PolyBase는 실패한 행의 백분율을 일정 간격으로 계산하므로 실패한 행의 실제 백분율은 reject_value를 초과할 수 있습니다.Since PolyBase computes the percentage of failed rows at intervals, the actual percentage of failed rows can exceed reject_value.

예:Example:

이 예제에서는 REJECT 옵션 세 개가 서로 상호 작용하는 방법을 보여 줍니다.This example shows how the three REJECT options interact with each other. 예를 들어REJECT_TYPE = percentage, REJECT_VALUE = 30 및 REJECT_SAMPLE_VALUE = 100인 경우 다음과 같은 시나리오가 일어날 수 있습니다.For example, if REJECT_TYPE = percentage, REJECT_VALUE = 30, and REJECT_SAMPLE_VALUE = 100, the following scenario could occur:

  • PolyBase가 처음 100개 행의 검색을 시도하며, 25개가 실패하고 75개가 성공합니다.PolyBase attempts to retrieve the first 100 rows; 25 fail and 75 succeed.

  • 실패한 행의 백분율은 25%로 계산되며, 이는 거부 값 30%보다 작습니다.Percent of failed rows is calculated as 25%, which is less than the reject value of 30%. 그러므로 PolyBase는 외부 데이터 소스에서 데이터 검색을 계속합니다.Hence, PolyBase will continue retrieving data from the external data source.

  • PolyBase는 다음 100개 행의 로드를 시도하며, 이번에는 25개가 성공하고 75개가 실패합니다.PolyBase attempts to load the next 100 rows; this time 25 succeed and 75 fail.

  • 실패한 행의 백분율은 50%로 다시 계산됩니다.Percent of failed rows is recalculated as 50%. 실패한 행의 이 백분율은 30% 거부 값을 초과했습니다.The percentage of failed rows has exceeded the 30% reject value.

  • PolyBase 쿼리는 처음 200개 행의 반환을 시도한 후 거부된 행이 50%여서 실패합니다.The PolyBase query fails with 50% rejected rows after attempting to return the first 200 rows. 참고로 PolyBase 쿼리에서 거부 임계값이 초과되었음을 감지하기 전에 일치하는 행 수가 반환되었습니다.Note that matching rows have been returned before the PolyBase query detects the reject threshold has been exceeded.

REJECTED_ROW_LOCATION = 디렉터리 위치REJECTED_ROW_LOCATION = Directory Location

거부된 행과 해당 오류 파일을 작성해야 하는 외부 데이터 원본 내 디렉터리를 지정합니다.Specifies the directory within the External Data Source that the rejected rows and the corresponding error file should be written. 지정된 경로가 존재하지 않을 경우 PolyBase는 자동으로 경로를 만듭니다.If the specified path does not exist, PolyBase will create one on your behalf. "rejectedrows"라는 이름의 하위 디렉터리가 생성됩니다. "" 문자는 위치 매개 변수에 명시적으로 명명되지 않는 한, 다른 데이터 처리를 위해 디렉터리를 이스케이프합니다.A child directory is created with the name "rejectedrows". The "" character ensures that the directory is escaped for other data processing unless explicitly named in the location parameter. 이 디렉터리 내에는 로드 제출 시간을 기준으로 YearMonthDay -HourMinuteSecond 형식에 따라 생성된 폴더가 있습니다(예:Within this directory, there is a folder created based on the time of load submission in the format YearMonthDay -HourMinuteSecond (Ex. 20180330-173205).20180330-173205). 이 폴더에서 _reason 파일과 data 파일이라는 두 가지 파일에 데이터가 기록됩니다.In this folder, two types of files are written, the _reason file and the data file.

reason 파일과 data 파일에는 모두 CTAS 문과 연결된 queryID가 있습니다.The reason files and the data files both have the queryID associated with the CTAS statement. data와 reason은 별도의 파일에 있으므로 해당 파일의 접미사가 일치해야 합니다.Because the data and the reason are in separate files corresponding files have a matching suffix.

분할된 데이터베이스 외부 테이블 옵션Sharded external table options
Elastic Database 쿼리에 대한 외부 데이터 원본(SQL Server 데이터 원본이 아닌) 및 배포 방법을 지정합니다.Specifies the external data source (a non-SQL Server data source) and a distribution method for the Elastic Database query.

DATA_SOURCEDATA_SOURCE
Hadoop 파일 시스템, Azure Blob Storage 또는 분할된 데이터베이스 맵 관리자에 저장된 데이터 같은 외부 데이터 원본입니다.An external data source such as data stored in a Hadoop File System, Azure blob storage, or a shard map manager.

SCHEMA_NAMESCHEMA_NAME
SCHEMA_NAME 절은 외부 테이블 정의를 원격 데이터베이스의 다른 스키마의 테이블에 매핑하는 기능을 제공합니다.The SCHEMA_NAME clause provides the ability to map the external table definition to a table in a different schema on the remote database. 이 기능을 사용하여 로컬과 원격 데이터베이스에 모두 존재하는 스키마 사이를 구분합니다.Use this to disambiguate between schemas that exist on both the local and remote databases.

OBJECT_NAMEOBJECT_NAME
OBJECT_NAME 절은 외부 테이블 정의를 원격 데이터베이스의 다른 이름을 가진 테이블에 매핑하는 기능을 제공합니다.The OBJECT_NAME clause provides the ability to map the external table definition to a table with a different name on the remote database. 이 기능을 사용하여 로컬과 원격 데이터베이스에 모두 존재하는 개체 이름 사이를 구분합니다.Use this to disambiguate between object names that exist on both the local and remote databases.

DISTRIBUTIONDISTRIBUTION
(선택 사항)Optional. 이 절은 SHARD_MAP_MANAGER 형식의 데이터베이스에 대해서만 필요합니다.This is only required only for databases of type SHARD_MAP_MANAGER. 이 절은 테이블이 분할된 데이터베이스 테이블로 처리되는지 아니면 복제된 테이블로 처리되는지를 제어합니다.This controls whether a table is treated as a sharded table or a replicated table. SHARDED(열 이름) 테이블에서는 서로 다른 테이블의 데이터가 겹치지 않습니다.With SHARDED (column name) tables, the data from different tables do not overlap. REPLICATED는 테이블이 모든 분할된 데이터베이스에서 같은 데이터를 갖도록 지정합니다.REPLICATED specifies that tables have the same data on every shard. ROUND_ROBIN은 응용 프로그램 관련 방법을 사용하여 데이터를 배포하도록 지정합니다.ROUND_ROBIN indicates that an application-specific method is used to distribute the data.

PermissionsPermissions

다음과 같은 권한이 필요합니다.Requires these user permissions:

  • CREATE TABLECREATE TABLE

  • ALTER ANY SCHEMAALTER ANY SCHEMA

  • ALTER ANY EXTERNAL DATA SOURCEALTER ANY EXTERNAL DATA SOURCE

  • ALTER ANY EXTERNAL FILE FORMATALTER ANY EXTERNAL FILE FORMAT

  • CONTROL DATABASECONTROL DATABASE

    참고로 외부 데이터 원본을 만드는 로그인은 Hadoop 또는 Azure Blob Storage에 있는 외부 데이터 원본에 대한 읽기 및 쓰기 권한을 가져야 합니다.Note, the login that creates the external data source must have permission to read and write to the external data source, located in Hadoop or Azure blob storage.

중요

ALTER ANY EXTERNAL DATA SOURCE 권한은 주체에 대해 외부 데이터 원본 개체를 만들고 수정하는 권한을 부여하며, 따라서 데이터베이스의 모든 데이터베이스 범위 자격 증명에 액세스하는 권한도 부여합니다.The ALTER ANY EXTERNAL DATA SOURCE permission grants any principal the ability to create and modify any external data source object, and therefore, it also grants the ability to access all database scoped credentials on the database. 이 권한은 높은 수준의 권한으로 간주되어야 하므로, 시스템의 신뢰할 수 있는 보안 주체에만 부여되어야 합니다.This permission must be considered as highly privileged, and therefore must be granted only to trusted principals in the system.

오류 처리Error Handling

CREATE EXTERNAL TABLE 문을 실행하는 동안 PolyBase는 외부 데이터 원본에 연결을 시도합니다.While executing the CREATE EXTERNAL TABLE statement, PolyBase attempts to connect to the external data source. 연결 시도가 실패하면 이 문이 실패하고 외부 테이블이 만들어지지 않습니다.If the attempt to connect fails, the statement will fail and the external table will not be created. PolyBase는 쿼리가 결국 실패할 때까지 연결을 다시 시도하므로 이 명령이 실패하려면 1분 이상 걸릴 수 있습니다.It can take a minute or more for the command to fail since PolyBase retries the connection before eventually failing the query.

일반적인 주의 사항General Remarks

임시 쿼리 시나리오, 즉 SELECT FROM EXTERNAL TABLE에서 PolyBase는 외부 데이터 원본에서 검색된 행을 임시 테이블에 저장합니다.In ad-hoc query scenarios, i.e. SELECT FROM EXTERNAL TABLE, PolyBase stores the rows retrieved from the external data source in a temporary table. 쿼리가 완료된 후 PolyBase는 임시 테이블을 제거하고 삭제합니다.After the query completes, PolyBase removes and deletes the temporary table. 영구적 데이터가 SQL 테이블에 저장되지 않습니다.No permanent data is stored in SQL tables.

이에 비해 가져오기 시나리오, 즉 SELECT INTO FROM EXTERNAL TABLE에서 PolyBase는 외부 데이터 원본에서 검색된 행을 영구 데이터로 SQL 테이블에 저장합니다.In contrast, in the import scenario, i.e. SELECT INTO FROM EXTERNAL TABLE, PolyBase stores the rows retrieved from the external data source as permanent data in the SQL table. 새 테이블은 PolyBase에서 외부 데이터를 검색할 때 쿼리 실행 중에 만들어집니다.The new table is created during query execution when PolyBase retrieves the external data.

PolyBase는 쿼리 성능을 향상시키기 위해 쿼리 계산 중 일부를 Hadoop에 푸시할 수 있습니다.PolyBase can push some of the query computation to Hadoop to improve query performance. 이 기능을 조건자 푸시 다운이라 합니다.This is called predicate pushdown. 이 기능을 활성화하려면 CREATE EXTERNAL DATA SOURCE (Transact-SQL)에 Hadoop 리소스 관리자 위치 옵션을 지정합니다.To enable this, specify the Hadoop resource manager location option in CREATE EXTERNAL DATA SOURCE (Transact-SQL).

같은 또는 서로 다른 외부 데이터 원본을 참조하는 수많은 외부 테이블을 만들 수 있습니다.You can create numerous external tables that reference the same or different external data sources.

제한 사항Limitations and Restrictions

CTP2에서 내보내기 기능은 지원되지 않습니다. 즉, SQL 데이터를 외부 데이터 원본에 영구적으로 저장합니다.In CTP2, the export functionality is not supported, i.e. permanently storing SQL data into the external data source. 이 기능은 CTP3에서 사용할 수 있습니다.This functionality will be available in CTP3.

외부 테이블에 대한 데이터는 어플라이언스 외부에 상주하므로 PolyBase의 제어를 받지 않으며 언제든지 외부 프로세스에 의해 변경 또는 제거될 수 있습니다.Since the data for an external table resides off the appliance, it is not under the control of PolyBase, and can be changed or removed at any time by an external process. 이러한 이유 때문에 외부 테이블에 대한 쿼리 결과는 결정적인 것으로 보증되지 않습니다.Because of this, uery results against an external table are not guaranteed to be deterministic. 즉, 외부 테이블에 대해 실행할 때마다 같은 쿼리가 서로 다른 결과를 반환할 수 있습니다.The same query can return different results each time it runs against an external table. 마찬가지로 외부 데이터가 제거되거나 이동되면 쿼리가 실패할 수 있습니다.Similarly, a query can fail if the external data is removed or relocated.

각각 서로 다른 외부 데이터 원본을 참조하는 여러 개의 외부 테이블을 만들 수 있습니다.You can create multiple external tables that each reference different external data sources. 그러나 서로 다른 Hadoop 데이터 원본에 대해 동시에 쿼리를 실행하는 경우 각 Hadoop 원본이 같은 'hadoop 연결' 서버 구성 설정을 사용해야 합니다.However, if you simultaneously run queries against different Hadoop data sources, then each Hadoop source must use the same 'hadoop connectivity' server configuration setting. 예를 들어 Cloudera Hadoop 클러스터 및 Hortonworks Hadoop 클러스터는 서로 다른 구성 설정을 사용하므로 이들에 대해 하나의 쿼리를 동시에 실행할 수 없습니다.For example, you can't simultaneously run a query against a Cloudera Hadoop cluster and a Hortonworks Hadoop cluster since these use different configuration settings. 구성 설정 및 지원되는 결합에 대해서는 PolyBase 연결 구성(Transact-SQL)을 참조하세요.For the configuration settings and supported combinations, see PolyBase Connectivity Configuration (Transact-SQL).

다음 DDL(Data Definition Language) 문은 외부 테이블에 대해 허용됩니다.Only these Data Definition Language (DDL) statements are allowed on external tables:

  • CREATE TABLE 및 DROP TABLECREATE TABLE and DROP TABLE

  • CREATE STATISTICS 및 DROP STATISTICSCREATE STATISTICS and DROP STATISTICS
    참고: 외부 테이블에 대한 CREATE 및 DROP STATISTICS는 Azure SQL Database에서 지원되지 않습니다.Note: CREATE and DROP STATISTICS on external tables are not supported in Azure SQL Database.

  • CREATE VIEW 및 DROP VIEWCREATE VIEW and DROP VIEW

    지원되지 않는 구문 및 작업:Constructs and operations not supported:

  • 외부 테이블 열에 대한 DEFAULT 제약 조건The DEFAULT constraint on external table columns

  • 삭제, 삽입 및 업데이트의 DML(데이터 조작 언어) 작업Data Manipulation Language (DML) operations of delete, insert, and update

    쿼리 제한 사항:Query limitations:

    PolyBase는 32개의 동시 PolyBase 쿼리를 실행할 때 폴더당 최대 33k 파일을 사용할 수 있습니다.PolyBase can consume a maximum of 33k files per folder when running 32 concurrent PolyBase queries. 이 최대 개수는 각 HDFS 폴더의 파일과 하위 폴더를 모두 포함합니다.This maximum number includes both files and subfolders in each HDFS folder. 동시성 수준이 32보다 작은 경우 사용자는 33k보다 많은 파일을 포함하는 HDFS의 폴더에 대해 PolyBase 쿼리를 실행할 수 있습니다.If the degree of concurrency is less than 32, a user can run PolyBase queries against folders in HDFS which contain more than 33k files. 외부 파일 경로를 짧게 유지하고 HDFS 폴더당 30k 이하의 파일을 사용하는 것이 좋습니다.We recommend that you keep external file paths short and use no more than 30k files per HDFS folder. 너무 많은 파일이 참조되면 JVM(Java Virtual Machine) 메모리 부족 예외가 발생할 수 있습니다.When too many files are referenced, a Java Virtual Machine (JVM) out-of-memory exception might occur.

테이블 너비 제한 사항: SQL Server 2016의 PolyBase에는 테이블 정의에 의해 유효한 단일 행의 최대 크기를 기반으로 32KB의 행 너비 한도가 적용됩니다.Table width limitations: PolyBase in SQL Server 2016 has a row width limit of 32KB based on the maximum size of a single valid row by table definition. 열 스키마의 합계가 32KB보다 큰 경우, PolyBase는 데이터를 쿼리할 수 없습니다.If the sum of the column schema is greater than 32KB, PolyBase will not be able to query the data.

SQL Data Warehouse에서 이 제한 사항은 1MB로 증가되었습니다.In SQL Data Warehouse, this limitation has been raised to 1MB.

잠금Locking

SCHEMARESOLUTION 개체에 대한 공유 잠금입니다.Shared lock on the SCHEMARESOLUTION object.

보안Security

외부 테이블에 대한 데이터 파일은 Hadoop 또는 Azure Blob Storage에 저장됩니다.The data files for an external table is stored in Hadoop or Azure blob storage. 이러한 데이터 파일은 사용자 고유의 프로세스에 의해 만들어지고 관리됩니다.These data files are created and managed by your own processes. 외부 데이터의 보안을 관리하는 것은 사용자의 책임입니다.It is your responsibility to manage the security of the external data.

Examples

1.A. 텍스트로 구분된 형식의 데이터를 사용하여 외부 테이블을 만듭니다.Create an external table with data in text-delimited format.

이 예제에서는 텍스트로 구분된 파일 형식의 데이터를 가진 외부 테이블을 만드는 데 필요한 모든 단계를 보여 줍니다.This example shows all the steps required to create an external table that has data formatted in text-delimited files. 즉, 외부 데이터 원본 mydatasource 및 외부 파일 형식 myfileformat을 만듭니다.It defines an external data source mydatasource and an external file format myfileformat. 이러한 데이터베이스 수준 개체는 나중에 CREATE EXTERNAL TABLE 문에 참조됩니다.These database-level objects are then referenced in the CREATE EXTERNAL TABLE statement. 자세한 내용은 CREATE EXTERNAL DATA SOURCE (Transact-SQL)CREATE EXTERNAL FILE FORMAT (Transact-SQL)을 참조하세요.For more information, see CREATE EXTERNAL DATA SOURCE (Transact-SQL) and CREATE EXTERNAL FILE FORMAT (Transact-SQL).


CREATE EXTERNAL DATA SOURCE mydatasource  
WITH (  
    TYPE = HADOOP,  
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'  
)  

CREATE EXTERNAL FILE FORMAT myfileformat  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,   
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')  
);  

CREATE EXTERNAL TABLE ClickStream (   
    url varchar(50),  
    event_date date,  
    user_IP varchar(50)  
)  
WITH (  
        LOCATION='/webdata/employee.tbl',  
        DATA_SOURCE = mydatasource,  
        FILE_FORMAT = myfileformat  
    )  
;  

2.B. RCFile 형식의 데이터를 사용하여 외부 테이블을 만듭니다.Create an external table with data in RCFile format.

이 예제에서는 RCFile 형식의 데이터를 가진 외부 테이블을 만드는 데 필요한 모든 단계를 보여 줍니다.This example shows all the steps required to create an external table that has data formatted as RCFiles. 즉, 외부 데이터 원본 mydatasource_rc 및 외부 파일 형식 myfileformat_rc를 만듭니다.It defines an external data source mydatasource_rc and an external file format myfileformat_rc. 이러한 데이터베이스 수준 개체는 나중에 CREATE EXTERNAL TABLE 문에 참조됩니다.These database-level objects are then referenced in the CREATE EXTERNAL TABLE statement. 자세한 내용은 CREATE EXTERNAL DATA SOURCE (Transact-SQL)CREATE EXTERNAL FILE FORMAT (Transact-SQL)을 참조하세요.For more information, see CREATE EXTERNAL DATA SOURCE (Transact-SQL) and CREATE EXTERNAL FILE FORMAT (Transact-SQL).


CREATE EXTERNAL DATA SOURCE mydatasource_rc  
WITH (  
    TYPE = HADOOP,  
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'  
)  

CREATE EXTERNAL FILE FORMAT myfileformat_rc  
WITH (  
    FORMAT_TYPE = RCFILE,  
    SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'  
)  
;  

CREATE EXTERNAL TABLE ClickStream_rc (   
    url varchar(50),  
    event_date date,  
    user_ip varchar(50)  
)  
WITH (  
        LOCATION='/webdata/employee_rc.tbl',  
        DATA_SOURCE = mydatasource_rc,  
        FILE_FORMAT = myfileformat_rc  
    )  
;  

3.C. ORC 형식의 데이터를 사용하여 외부 테이블을 만듭니다.Create an external table with data in ORC format.

이 예제에서는 ORC 형식의 데이터를 가진 외부 테이블을 만드는 데 필요한 모든 단계를 보여 줍니다.This example shows all the steps required to create an external table that has data formatted as ORC files. 즉, 외부 데이터 원본 mydatasource_orc 및 외부 파일 형식 myfileformat_orc를 만듭니다.It defines an external data source mydatasource_orc and an external file format myfileformat_orc. 이러한 데이터베이스 수준 개체는 나중에 CREATE EXTERNAL TABLE 문에 참조됩니다.These database-level objects are then referenced in the CREATE EXTERNAL TABLE statement. 자세한 내용은 CREATE EXTERNAL DATA SOURCE (Transact-SQL)CREATE EXTERNAL FILE FORMAT (Transact-SQL)을 참조하세요.For more information, see CREATE EXTERNAL DATA SOURCE (Transact-SQL) and CREATE EXTERNAL FILE FORMAT (Transact-SQL).


CREATE EXTERNAL DATA SOURCE mydatasource_orc  
WITH (  
    TYPE = HADOOP,  
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'  
)  

CREATE EXTERNAL FILE FORMAT myfileformat_orc  
WITH (  
    FORMAT = ORC,  
    COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'  
)  
;  

CREATE EXTERNAL TABLE ClickStream_orc (   
    url varchar(50),  
    event_date date,  
    user_ip varchar(50)  
)  
WITH (  
        LOCATION='/webdata/',  
        DATA_SOURCE = mydatasource_orc,  
        FILE_FORMAT = myfileformat_orc  
    )  
;  

4.D. Hadoop 데이터 쿼리Querying Hadoop data

Clickstream은 Hadoop 클러스터의 텍스트로 구분된 파일 employee.tbl에 연결하는 외부 테이블입니다.Clickstream is an external table that connects to the employee.tbl delimited text file on a Hadoop cluster. 다음 쿼리는 표준 테이블에 대한 쿼리와 유사해 보입니다.The following query looks just like a query against a standard table. 그러나 이 쿼리는 Hadoop에서 데이터를 검색한 다음, 결과를 계산합니다.However, this query retrieves data from Hadoop and then computes the restuls.

SELECT TOP 10 (url) FROM ClickStream WHERE user_ip = 'xxx.xxx.xxx.xxx'  
;  

5.E. Hadoop 데이터를 SQL 데이터와 조인Join Hadoop data with SQL data

이 쿼리는 SQL 테이블 두 개에 대한 표준 JOIN과 유사해 보입니다.This query looks just like a standard JOIN on two SQL tables. 차이점은 PolyBase이 Hadoop에서 Clickstream 데이터를 검색한 다음, UrlDescription 테이블에 조인한다는 것입니다.The difference is that PolyBase retrieves the Clickstream data from Hadoop and then joins it to the UrlDescription table. 한 테이블은 외부 테이블이며 다른 테이블은 표준 SQL 테이블입니다.One table is an external table and the other is a standard SQL table.

SELECT url.description  
FROM ClickStream cs  
JOIN UrlDescription url ON cs.url = url.name  
WHERE cs.url = 'msdn.microsoft.com'  
;  

6.F. SQL 테이블로 Hadoop의 데이터 가져오기Import data from Hadoop into a SQL table

이 예제에서는 표준 SQL 테이블 user와 외부 테이블 ClickStream 간 조인의 결과를 영구적으로 저장하는 새 SQL 케이블 ms_user입니다.This example creates a new SQL table ms_user that permanently stores the result of a join between the standard SQL table user and the external table ClickStream.

SELECT DISTINCT user.FirstName, user.LastName  
INTO ms_user  
FROM user INNER JOIN (  
    SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com'  
    ) AS ms_user  
ON user.user_ip = ms.user_ip  
;  

7.G. 분할된 데이터베이스 데이터 원본에 대한 외부 테이블 만들기Create an external table for a sharded data source

이 예제에서는 SCHEMA_NAME 및 OBJECT_NAME 절을 사용하여 원격 DMV를 외부 테이블에 다시 매핑합니다.This example remaps a remote DMV to an external table using the SCHEMA_NAME and OBJECT_NAME clauses.

CREATE EXTERNAL TABLE [dbo].[all_dm_exec_requests]([session_id] smallint NOT NULL,  
  [request_id] int NOT NULL,  
  [start_time] datetime NOT NULL,   
  [status] nvarchar(30) NOT NULL,  
  [command] nvarchar(32) NOT NULL,  
  [sql_handle] varbinary(64),  
  [statement_start_offset] int,  
  [statement_end_offset] int,  
  [cpu_time] int NOT NULL)  
WITH  
(  
  DATA_SOURCE = MyExtSrc,  
  SCHEMA_NAME = 'sys',  
  OBJECT_NAME = 'dm_exec_requests',  
  DISTRIBUTION=ROUND_ROBIN  
);   

예제: Azure SQL 데이터 웨어하우스Azure SQL Data Warehouse병렬 데이터 웨어하우스Parallel Data WarehouseExamples: Azure SQL 데이터 웨어하우스Azure SQL Data Warehouse and 병렬 데이터 웨어하우스Parallel Data Warehouse

8.H. Azure SQL 데이터 웨어하우스SQL Data Warehouse로 ADLS의 데이터 가져오기Importing Data from ADLS into Azure SQL 데이터 웨어하우스SQL Data Warehouse


-- These values come from your Azure Active Directory Application used to authenticate to ADLS
CREATE DATABASE SCOPED CREDENTIAL ADLUser 
WITH IDENTITY = '<clientID>@\<OAuth2.0TokenEndPoint>',
SECRET = '<KEY>' ;


CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (TYPE = HADOOP,
      LOCATION = 'adl://pbasetr.azuredatalakestore.net'
)



CREATE EXTERNAL FILE FORMAT TextFileFormat 
WITH
(
    FORMAT_TYPE = DELIMITEDTEXT 
    , FORMAT_OPTIONS ( FIELDTERMINATOR = '|' 
                     , STRINGDELIMITER = '' 
                     , DATEFORMAT = 'yyyy-MM-dd HH:mm:ss.fff' 
                     , USETYPE_DEFAULT = FALSE 
                     ) 
)


CREATE EXTERNAL TABLE [dbo].[DimProductexternal] 
( [ProductKey] [int] NOT NULL, 
  [ProductLabel] nvarchar NULL, 
  [ProductName] nvarchar NULL ) 
WITH
(
    LOCATION='/DimProduct/' , 
    DATA_SOURCE = AzureDataLakeStore , 
    FILE_FORMAT = TextFileFormat , 
    REJECT_TYPE = VALUE ,
    REJECT_VALUE = 0
) ;


CREATE TABLE [dbo].[DimProduct] 
WITH (DISTRIBUTION = HASH([ProductKey] ) ) 
AS SELECT * FROM 
[dbo].[DimProduct_external] ; 

9.I. 외부 테이블 조인Join external tables

SELECT url.description  
FROM ClickStream cs  
JOIN UrlDescription url ON cs.url = url.name  
WHERE cs.url = 'msdn.microsoft.com'  
;  

10.J. HDFS 데이터를 PDW 데이터와 조인Join HDFS data with PDW data

SELECT cs.user_ip FROM ClickStream cs  
JOIN User u ON cs.user_ip = u.user_ip  
WHERE cs.url = 'www.microsoft.com'  
;  

11.K. HDFS의 행 데이터를 배포된 PDW 테이블에 가져오기Import row data from HDFS into a distributed PDW Table

CREATE TABLE ClickStream_PDW  
WITH ( DISTRIBUTION = HASH (url) )  
AS SELECT url, event_date, user_ip FROM ClickStream  
;  

12.L. HDFS의 행 데이터를 복제된 PDW 테이블에 가져오기Import row data from HDFS into a replicated PDW Table

CREATE TABLE ClickStream_PDW  
WITH ( DISTRIBUTION = REPLICATE )  
AS SELECT url, event_date, user_ip   
FROM ClickStream  
;  

참고 항목See Also

일반적인 메타데이터 쿼리 예제(SQL Server PDW) Common Metadata Query Examples (SQL Server PDW)
CREATE EXTERNAL DATA SOURCE(Transact-SQL) CREATE EXTERNAL DATA SOURCE (Transact-SQL)
CREATE EXTERNAL FILE FORMAT(Transact-SQL) CREATE EXTERNAL FILE FORMAT (Transact-SQL)
CREATE EXTERNAL TABLE AS SELECT (Transact-SQL) CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
CREATE TABLE AS SELECT (Azure SQL Data Warehouse)CREATE TABLE AS SELECT (Azure SQL Data Warehouse)