External Table Properties

From ER/Studio Data Architect
Jump to: navigation, search

Go Up to Defining Table Storage

The External Table Properties dialog allows you to manage external table properties for select databases. External tables allow you to query data stored in flat files that are outside of the database. Make any necessary updates, and then click OK to save your changes, which then are reflected in the DDL.

Microsoft SQL Server / Azure Synapse Options

  • Location. 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, which is the data location specified in the external data source.
  • Data Source. Specifies the name of the external data source that contains the location of the external data.
  • File Format. Specifies the name of the external file format object that stores the file type and compression method for the external data.
  • Reject Type. Clarifies whether the REJECT_VALUE option is specified as a literal value or a percentage.
  • Reject Value. Specifies the value or the percentage of rows that can be rejected before the query fails.
    • Value. For REJECT_TYPE = value, REJECT_VALUE must be an integer between 0 and 2,147,483,647.
    • Percentage. For REJECT_TYPE = percentage, REJECT_VALUE must be a floating value between 0 and 100.
  • Reject Sample Value. Specifies the value or the percentage of rows that can be rejected before the query fails. The reject_sample_value parameter must be an integer between 0 and 2,147,483,647.
  • Reject Row Location. Specifies the directory within the External Data Source where you want the rejected rows and the corresponding error file to reside.

Microsoft Azure SQL DB Options

  • Data Source. Defines the external data source (a shard map) used for the external table.
  • Schema Name. Maps the external table definition to a table in a different schema. If omitted, the schema of the remote object is assumed to be dbo, and its name is assumed to be identical to the defined external table name.
  • Object Name. Maps the external table definition to a table.
  • Distribution. When using a database of type SHARD_MAP_MANAGER, the DISTRIBUTION clause specifies the data distribution used for this table. The query processor utilizes the information provided in this area to build the most efficient query plans. Options include:
    • Sharded. Specifies that data is horizontally partitioned across the databases. The partitioning key for the data distribution is the sharding_column_name parameter.
    • Replicated. Specifies that identical copies of the table are present on each database. It is the user's responsibility to ensure that the replicas are identical across the databases.
    • Round Robin. Default. Specifies that the table is horizontally partitioned using an application-dependent distribution method.

Oracle 12c and Later Options

  • Access Driver. Options include ORACLE_LOADER and ORACLE_DATAPUMP.
    • ORACLE_LOADER (Default) The ORACLE_LOADER driver performs only data loads and that data must come from text datafiles. Loads from external tables to internal tables occur by reading from the external tables' text-only datafiles.
    • ORACLE_DATAPUMP The ORACLE_DATAPUMP can perform both loads and unloads, but the data must come from binary dump files. Loads to internal tables from external tables occur by fetching from the binary dump files while unloads from internal tables to external tables occur by populating the external tables' binary dump files.
  • Default Directory. Specifies the default directory object location of files read or written by external tables.
  • Access Parameters. Specifies the external data source and implements the external table type. Each type of external table has its own access driver that provides access parameters unique to that type of external table.
Retrieved from "https://docwiki.embarcadero.com/ERStudioDA/206/e/index.php?title=External_Table_Properties&oldid=8257"