As part of Autoschema Generic data type for NOS feature, GENERIC_DATATYPE, STRING_SIZE and BINARY_SIZE NVPs are available to use when creating foreign table. This example shows how to use GENERIC_DATATYPE, STRING_SIZE, and BINARY_SIZE to define CHAR/VARCHAR and BYTE/VARBYTE column sizes. The default size is 4000 and can go up to a maximum of 64000 for Latin characters and 32000 for Unicode characters.
To manage this at the system level, TwoDBSControl flags are available. One flag enables and disables the feature and the other one is to select the appropriate conversion type as needed. When you enable the feature and do not specify STRING_SIZE/BINARY_SIZE value, the system automatically applies a default size of 4,000 for char/varchar, byte/varbyte columns and integer data type as BigInt and Decimals as number or float based on total number of digits.
The default value for AutoschemaGenericDT DBSControl is TRUE. Setting GENERIC_DATATYPE NVP to TRUE creates a new table with the functionality. The DBSControl flag and GENERIC_DATATYPE NVP must be set to TRUE during Create Foreign Tables and Read_NOS to enable this setting.
tdwprintf(_T("275. AutoschemaGenericDT - This flag is used to enable or disable Autoschema Generic Datatype on NOS tables.\n"));
tdwprintf(_T(" TRUE (default) : Enabled.\n"));
tdwprintf(_T(" FALSE : Disabled .\n"));
tdwprintf(_T("\n"));
tdwprintf(_T("276. EnableDatatypeConversion = %d (Default 0).\n"));
tdwprintf(_T("\n This flag controls the Datatype Conversions when AutoschemaGenericDT flag enabled.\n"));
tdwprintf(_T(" Value Bits Meaning \n"));
tdwprintf(_T(" ----- -------- -------------------------------------------------------------------------------- \n"));
tdwprintf(_T("\n 1: 00000001 Disables conversion of STRING_SIZE to the desired size for both CSV and Parquet data."));
tdwprintf(_T("\n 2: 00000010 Disables conversion of BINARY_SIZE to the desired size for both CSV and Parquet data."));
tdwprintf(_T("\n 4: 00000100 Disables conversion of NUMERIC data type to BIGINT for CSV data."));
tdwprintf(_T("\n 8: 00001000 Disables conversion of DECIMAL data type to NUMBER for CSV data."));
tdwprintf(_T("\n 16: 00010000 Disables conversion of DECIMAL data type to FLOAT for CSV data.\n\n"));
CREATE FOREIGN TABLE Query
create AUTHORIZATION TDAWSEDBS_AUTH1
USER 'AKIASCM5IBMEZTK2M5LD'
PASSWORD 'mXJ1x20N6+fwX7OuWcAaKpdEq1q/ffg4hzKO/023';
*** Authorization has been created.
*** Total elapsed time was 1 second.
CREATE FOREIGN TABLE tab01_base,
EXTERNAL SECURITY TDAWSEDBS_AUTH1
USING
(
LOCATION ('/s3/s3.amazonaws.com/sb255121/AGD/small_15recs.csv')
GENERIC_DATATYPE('TRUE') --New NVP
STRING_SIZE('6') --New NVP
BINARY_SIZE('6') -- New NVP
)
NO PRIMARY INDEX ;
*** Table has been created.
*** Total elapsed time was 2 seconds.
show table tab01_base;
*** Text of DDL statement returned.
*** Total elapsed time was 1 second.
--------------------------------------------------------------------------------------------------------
CREATE MULTISET FOREIGN TABLE TEST1.tab01_base ,FALLBACK ,
EXTERNAL SECURITY TEST1.TDAWSEDBS_AUTH1 ,
MAP = TD_MAP1
(
Location VARCHAR(2048) CHARACTER SET UNICODE CASESPECIFIC,
A BIGINT,
B VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC,
C VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC,
D VARBYTE(6),
E FLOAT,
F NUMBER)
USING
(
LOCATION ('/s3/s3.amazonaws.com/sb255121/AGD/small_15recs.csv')
GENERIC_DATATYPE ('TRUE')
STRING_SIZE ('6')
BINARY_SIZE ('6')
MANIFEST ('FALSE')
PATHPATTERN ('$var1/$var2')
ROWFORMAT ('{"field_delimiter":",","record_delimiter":"\n","character_set":"LATIN"}')
STOREDAS ('TEXTFILE')
HEADER ('TRUE')
STRIP_EXTERIOR_SPACES ('FALSE')
STRIP_ENCLOSING_CHAR ('NONE')
)
NO PRIMARY INDEX ;
sel A,B,C,D,E,F from tab01_base;
*** Query completed. 15 rows found. 6 columns returned.
*** Total elapsed time was 1 second.
A B C D E F
-------------------- ------ ------ ------------ ---------------------- ----------------------------------------
1 aaaa 1aaaaa 1A04 1.23456000000000E-001 .123456789012345678901
2 bbbbb 2bbbbb 1A14 1.12345600000000E 000 1.123456789012345678901
3 ccccc 3ccccc 1A24 2.12345600000000E 000 2.123456789012345678901
4 ddddd 4ddddd 1A34 3.12345600000000E 000 3.123456789012345678901
5 eeeee 5eeeee 1A44 4.12345600000000E 000 4.123456789012345678901
*** Warning: RetLimit exceeded.
Cancelling the request.
BTEQ -- Enter your SQL request or BTEQ command: