CREATE EXTERNAL TABLE (Transact-SQL)
79 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the purpose of the CREATE EXTERNAL DATA SOURCE statement in the provided text?

  • To define a connection to a Hadoop cluster (correct)
  • To define a connection to a SQL Server database
  • To define a connection to an Azure Blob Storage account
  • To define a connection to an Amazon S3 bucket

    • What is the purpose of the CREATE EXTERNAL FILE FORMAT statement in the provided text?

  • To define the format of data files in a SQL Server database
  • To define the format of data files in an Amazon S3 bucket
  • To define the format of data files in an Azure Blob Storage account
  • To define the format of data files in a Hadoop cluster (correct)

    • What is the purpose of the CREATE EXTERNAL TABLE statement in the provided text?

  • To create a table in a SQL Server database
  • To create a table in an Azure Blob Storage account
  • To create a table in an Amazon S3 bucket
  • To create a table in a Hadoop cluster (correct)

    • What is the purpose of the CREATE DATABASE SCOPED CREDENTIAL statement in the provided text?

    <p>To create a credential for accessing a SQL Server database</p> Signup and view all the answers

    What is the purpose of the CREATE MASTER KEY statement in the provided text?

    <p>To create a master key for encrypting data in a SQL Server database</p> Signup and view all the answers

    What is the purpose of the SELECT INTO statement in the provided text?

    <p>To create a new SQL Server table from the results of a query</p> Signup and view all the answers

    What is the purpose of the JOIN statement in the provided text?

    <p>To join data from a Hadoop cluster with data from a SQL Server database</p> Signup and view all the answers

    What is the purpose of the PUSHDOWN option in the CREATE EXTERNAL DATA SOURCE statement?

    <p>To enable push-down of predicates and aggregations to the Hadoop cluster</p> Signup and view all the answers

    What is the purpose of the LOCATION parameter in the CREATE EXTERNAL DATA SOURCE statement?

    <p>To specify the location of the Hadoop cluster</p> Signup and view all the answers

    What is the purpose of the CREDENTIAL parameter in the CREATE EXTERNAL DATA SOURCE statement?

    <p>To specify the credentials for accessing the SQL Server database</p> Signup and view all the answers

    What does SQL store for an external table?

    <p>Only the table metadata</p> Signup and view all the answers

    Why is it recommended to provide a three-part name for an external table?

    <p>For best performance</p> Signup and view all the answers

    What happens if the column definitions in an external table don't match the data in the external files?

    <p>File rows will be rejected</p> Signup and view all the answers

    In PolyBase, what happens when a query selects from an external table with a specified folder location?

    <p>Files are retrieved from the folder and subfolders</p> Signup and view all the answers

    What does PolyBase not return when querying from an external table?

    <p>.hidden files</p> Signup and view all the answers

    Where is the core-site.xml configuration file located for changing PolyBase settings?

    <p>\PolyBase\Hadoop\Conf\bin root of SQL Server</p> Signup and view all the answers

    Which clause defines the external data source (a shard map) that is used for the external table?

    <p>DATA_SOURCE</p> Signup and view all the answers

    Which data distribution method indicates that an application-specific method is used to distribute the data?

    <p>ROUND_ROBIN</p> Signup and view all the answers

    Which data type is not supported for columns in external tables for Azure SQL Database?

    <p>text</p> Signup and view all the answers

    What is the purpose of the SCHEMA_NAME and OBJECT_NAME clauses?

    <p>To map the external table definition to a table in a different schema</p> Signup and view all the answers

    Which data distribution method specifies that identical copies of the table are present on each database?

    <p>REPLICATED</p> Signup and view all the answers

    What is the purpose of the DISTRIBUTION clause in the CREATE EXTERNAL TABLE statement?

    <p>To specify the data distribution method</p> Signup and view all the answers

    What happens if the column definitions, including the data types and number of columns, do not match the data in the external files?

    <p>The file rows will be rejected when querying the actual data</p> Signup and view all the answers

    Which data distribution method specifies that the data from different tables don't overlap?

    <p>SHARDED</p> Signup and view all the answers

    Which clause allows you to configure column name, data type, nullability, and collation for an external table?

    <p>[ ,...n ]</p> Signup and view all the answers

    Which of the following is true about the DEFAULT CONSTRAINT on external tables in Azure SQL Database?

    <p>You can't use the DEFAULT CONSTRAINT on external tables</p> Signup and view all the answers

    What is the purpose of using GRANT or REVOKE statements for external tables?

    <p>To control access privileges to the external data source</p> Signup and view all the answers

    How does SQL Database handle data retrieval from an external data source in an ad hoc query scenario?

    <p>It creates a temporary table to store the retrieved data during query execution</p> Signup and view all the answers

    What happens to the temporary table created during an ad hoc query against an external table?

    <p>It is automatically dropped after the query completes</p> Signup and view all the answers

    In the import scenario, where does SQL Database store the data retrieved from an external data source?

    <p>In a permanent SQL table created during query execution</p> Signup and view all the answers

    What is a limitation of querying external tables compared to querying regular SQL Server tables?

    <p>Queries against external tables do not adhere to isolation semantics</p> Signup and view all the answers

    Which Data Definition Language (DDL) statements are allowed on external tables?

    <p>CREATE TABLE, DROP TABLE, CREATE VIEW, DROP VIEW</p> Signup and view all the answers

    Which of the following is the correct syntax for creating a master key in SQL Server?

    <p>CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';</p> Signup and view all the answers

    What is the purpose of creating a database scoped credential in SQL Server?

    <p>To grant access to external data sources</p> Signup and view all the answers

    Which of the following is the correct syntax for creating an external data source in SQL Server?

    <p>CREATE EXTERNAL DATA SOURCE source_name WITH (LOCATION = 'oracle://[:]', CREDENTIAL = credential_name);</p> Signup and view all the answers

    What is the purpose of the PUSHDOWN option when creating an external data source in SQL Server?

    <p>To enable or disable query pushdown optimization</p> Signup and view all the answers

    Which of the following is the correct syntax for creating an external table in SQL Server for Oracle?

    <p>CREATE EXTERNAL TABLE customers(...) WITH (LOCATION = 'DB1.mySchema.customer', DATA_SOURCE = external_data_source_name);</p> Signup and view all the answers

    What is the purpose of the LOCATION option when creating an external table in SQL Server?

    <p>To specify the location or path of the external data</p> Signup and view all the answers

    Which of the following is the correct syntax for creating an external file format in SQL Server for Parquet files?

    <p>CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH (FORMAT_TYPE = PARQUET);</p> Signup and view all the answers

    What is the purpose of the FILE_FORMAT option when creating an external table in SQL Server?

    <p>To specify the file format of the external data</p> Signup and view all the answers

    Which of the following is the correct syntax for querying a Parquet file stored in S3-compatible object storage via an external table in SQL Server?

    <p>SELECT * FROM Region;</p> Signup and view all the answers

    What is the purpose of the sp_data_source_objects system stored procedure in SQL Server?

    <p>To retrieve metadata about external data sources and external tables</p> Signup and view all the answers

    What is the purpose of the matching suffix for corresponding data and reason files?

    <p>To ensure the files are related and can be easily identified</p> Signup and view all the answers

    Which of the following permissions is not required to create an external table in SQL Server?

    <p>CREATE TABLE</p> Signup and view all the answers

    What is the purpose of the DATABASE SCOPED CREDENTIAL used in the CREATE EXTERNAL TABLE command?

    <p>To provide authentication credentials for the external data source</p> Signup and view all the answers

    When using an external table to export data to a Hadoop or Azure Storage external data source, what additional permission is required for the login specified in the DATABASE SCOPED CREDENTIAL?

    <p>WRITE</p> Signup and view all the answers

    What is the purpose of the ALTER ANY EXTERNAL DATA SOURCE permission?

    <p>Both a and b</p> Signup and view all the answers

    What happens if the attempt to connect to the external data source fails when executing the CREATE EXTERNAL TABLE statement?

    <p>The statement will fail, and the external table won't be created</p> Signup and view all the answers

    What is the difference between ad hoc query scenarios and import scenarios when using an external table in SQL Server?

    <p>In ad hoc queries, PolyBase stores the retrieved rows in a temporary table, while in import scenarios the new table is created during query execution</p> Signup and view all the answers

    What is the purpose of the Hadoop resource manager location option in the CREATE EXTERNAL DATA SOURCE statement?

    <p>To enable predicate pushdown to improve query performance</p> Signup and view all the answers

    What is a limitation of using external tables in SQL Server?

    <p>Query results against an external table are not guaranteed to be deterministic</p> Signup and view all the answers

    What is the restriction on simultaneously running queries against different Hadoop data sources in SQL Server?

    <p>The queries must use the same Hadoop connectivity server configuration setting</p> Signup and view all the answers

    What is the purpose of the REJECT_TYPE option in PolyBase?

    <p>To specify whether the REJECT_VALUE option is a literal value or a percentage</p> Signup and view all the answers

    What is the purpose of the REJECT_VALUE option in PolyBase?

    <p>To specify the value or the percentage of rows that can be rejected before the query fails</p> Signup and view all the answers

    What is the purpose of the REJECT_SAMPLE_VALUE option in PolyBase?

    <p>To determine the number of rows to attempt to retrieve before PolyBase recalculates the percentage of rejected rows</p> Signup and view all the answers

    What happens when the percentage of rejected rows exceeds the REJECT_VALUE in PolyBase?

    <p>The query will return partial results until the reject threshold is exceeded, then fail with an error message</p> Signup and view all the answers

    What is the purpose of the REJECTED_ROW_LOCATION option in PolyBase?

    <p>To specify the directory within the External Data Source where the rejected rows and error files should be written</p> Signup and view all the answers

    What is the format of the directory created for the rejected rows and error files in PolyBase?

    <p>The directory is named based on the time of load submission in the format YearMonthDay-HourMinuteSecond</p> Signup and view all the answers

    What is the purpose of the "_" character in the REJECTED_ROW_LOCATION directory name?

    <p>To ensure the directory is escaped for other data processing</p> Signup and view all the answers

    Which external data sources can use the REJECTED_ROW_LOCATION option in PolyBase?

    <p>Only external data sources where TYPE = HADOOP and for external tables using DELIMITEDTEXT FORMAT_TYPE</p> Signup and view all the answers

    What types of files are written in the REJECTED_ROW_LOCATION directory in PolyBase?

    <p>Both the rejected rows files and the error files</p> Signup and view all the answers

    How does PolyBase calculate the percentage of failed rows when using the REJECT_TYPE = percentage option?

    <p>PolyBase calculates the percentage of failed rows at intervals, after attempting to retrieve the number of rows specified by the REJECT_SAMPLE_VALUE option</p> Signup and view all the answers

    What happens if the percentage of failed rows exceeds the REJECT_VALUE when using the REJECT_TYPE = percentage option in PolyBase?

    <p>The query will return partial results until the reject threshold is exceeded, then fail with an error message</p> Signup and view all the answers

    What is the primary purpose of creating an external table using the CREATE EXTERNAL TABLE command?

    <p>To provide access to data stored in a Hadoop cluster or Azure Blob Storage</p> Signup and view all the answers

    Which of the following is a primary use case for external data sources when used with an external table?

    <p>Data virtualization and data load using PolyBase</p> Signup and view all the answers

    What is the minimum version of SQL Server required to use the CREATE EXTERNAL TABLE command?

    <p>SQL Server 2016</p> Signup and view all the answers

    Which of the following statements is true about the syntax conventions used in the CREATE EXTERNAL TABLE command?

    <p>The syntax conventions are specific to Transact-SQL</p> Signup and view all the answers

    Which of the following is a secondary use case for external data sources when used with an external table?

    <p>Bulk load operations using SQL Server or SQL Database using BULK INSERT or OPENROWSET</p> Signup and view all the answers

    What is the primary purpose of the DROP EXTERNAL TABLE command mentioned in the text?

    <p>To remove the external table definition from the SQL Server database</p> Signup and view all the answers

    Which of the following statements is NOT allowed on external tables in PolyBase?

    <p>INSERT</p> Signup and view all the answers

    What is the maximum number of files PolyBase can consume per folder when running 32 concurrent queries?

    <p>33k files</p> Signup and view all the answers

    Which of the following data types CANNOT be used in PolyBase external tables?

    <p>Both geography and text</p> Signup and view all the answers

    What is the row width limit for PolyBase in SQL Server 2016?

    <p>32 KB</p> Signup and view all the answers

    Which of the following statements is true about creating external tables to MongoDB collections that contain arrays?

    <p>Both of the above approaches are valid</p> Signup and view all the answers

    What type of lock is acquired on the SCHEMARESOLUTION object when working with PolyBase external tables?

    <p>Shared lock</p> Signup and view all the answers

    Which of the following statements is true about the security of data files for an external table in PolyBase?

    <p>It is your responsibility to manage the security of the external data files</p> Signup and view all the answers

    In the provided example for creating an external table with data in text-delimited format, what is the value of the FIELD_TERMINATOR option in the myfileformat external file format?

    <p>'|'</p> Signup and view all the answers

    Which of the following statements is true about the LOCATION option when creating an external table in PolyBase?

    <p>It specifies the path to the external data files</p> Signup and view all the answers

    In the provided example for creating an external table with data in RCFile format, what is the value of the FORMAT_TYPE option in the myfileformat_rc external file format?

    <p>RCFILE</p> Signup and view all the answers

    Study Notes

    External Data Sources and Tables

    • CREATE EXTERNAL DATA SOURCE statement creates a reference to an external data source.
    • CREATE EXTERNAL FILE FORMAT statement defines the format of an external file.
    • CREATE EXTERNAL TABLE statement creates an external table, which is a virtual table that references data in an external data source.

    Security and Authentication

    • CREATE DATABASE SCOPED CREDENTIAL statement creates a credential that can be used to authenticate to an external data source.
    • CREATE MASTER KEY statement creates a master key, which is used to encrypt the credential.
    • CREDENTIAL parameter in CREATE EXTERNAL DATA SOURCE statement specifies the credential to use for authentication.

    External Tables

    • External tables store metadata about the external data, not the data itself.
    • Providing a three-part name for an external table is recommended for clarity and unambiguity.
    • If column definitions in an external table don't match the data in the external files, the query will fail.
    • PolyBase returns only the rows that match the specified folder location when querying an external table.

    Querying External Tables

    • SELECT INTO statement is used to import data from an external table into a regular SQL Server table.
    • JOIN statement can be used to combine data from an external table with data from a regular SQL Server table.
    • PolyBase does not return data that is not present in the external files when querying an external table.

    PolyBase Settings

    • The core-site.xml configuration file is used to change PolyBase settings.
    • The PUSHDOWN option in CREATE EXTERNAL DATA SOURCE statement enables pushdown operations.
    • The LOCATION parameter in CREATE EXTERNAL DATA SOURCE statement specifies the location of the external data source.

    Data Distribution

    • The DISTRIBUTION clause in CREATE EXTERNAL TABLE statement specifies how the data is distributed.
    • The SCHEMA_NAME and OBJECT_NAME clauses define the schema and name of the external table.
    • The REPLICATED distribution method specifies that identical copies of the table are present on each database.

    Data Types and Constraints

    • The DEFAULT CONSTRAINT on external tables in Azure SQL Database is not supported.
    • The GRANT and REVOKE statements are used to manage permissions on external tables.
    • External tables have limitations compared to regular SQL Server tables, such as data type restrictions.

    Ad Hoc Queries and Import Scenarios

    • In ad hoc query scenarios, SQL Database stores the data in a temporary table.
    • In import scenarios, SQL Database stores the data in a regular SQL Server table.

    Creating External Tables

    • The correct syntax for creating an external table in SQL Server for Oracle is CREATE EXTERNAL TABLE ... FOR Oracle.
    • The LOCATION option specifies the location of the external table.
    • The FILE_FORMAT option specifies the format of the external files.

    Querying External Tables

    • The correct syntax for querying a Parquet file stored in S3-compatible object storage via an external table in SQL Server is SELECT ... FROM ... LOCATION ....
    • The sp_data_source_objects system stored procedure is used to manage external data sources.

    Security and Permissions

    • The DATABASE SCOPED CREDENTIAL is used to authenticate to an external data source.
    • The ALTER ANY EXTERNAL DATA SOURCE permission is required to create an external table.
    • Additional permissions are required to export data to a Hadoop or Azure Storage external data source.

    PolyBase Options

    • The REJECT_TYPE option specifies the type of error handling for rejected rows.
    • The REJECT_VALUE option specifies the value for error handling.
    • The REJECTED_ROW_LOCATION option specifies the location for rejected rows and error files.

    External Data Sources

    • The Hadoop resource manager location option specifies the location of the Hadoop resource manager.
    • External data sources can include Hadoop, Azure Storage, and Oracle.
    • External tables can be used to export data to external data sources.

    Studying That Suits You

    Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

    Quiz Team

    Description

    Learn how to create an external table using Transact-SQL with syntax, arguments, remarks, permissions, and examples provided for SQL products. Choose your preferred SQL product to view specific information. This command is commonly used in Azure SQL Managed Instance and SQL Server.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser