|
Connection String Attributes
|
 
|
|
Connection String Attributes
|
|
Driver={ZappySys SFTP JSON Driver}[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;]
Syntax: (Using DSN)
DSN=YourDsnName;[,Attribute1=Value;][,Attribute2=Value;]........[,Attribute3=Value;]
| Attribute | Label | Description | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| AccessMode (Setting of JsonConfigurationParams) | AccessMode | Defines how to read data from file path/URL or direct string
| ||||||||||||||||||||||||||
| ConnectionStringFile (Setting of JsonConfigurationParams) | ConnectionStringFile | |||||||||||||||||||||||||||
| Data (Setting of JsonConfigurationParams) | DirectValue | Direct String you like to use as a datasource. This attribute always takes precedence if both Data and Src defined (i.e. File/Url). You can also use AccessMode=1 along with Data='...' in WITH clause to use Direct String rather than URL/File. | ||||||||||||||||||||||||||
| EnableCustomReplace (Setting of JsonConfigurationParams) | EnableCustomReplace | Enables custom search / replace in the document text after its read from the file/url or direct string. This replace operation happens before its parsed. This option can be useful for custom escape sequence in source document which is causing issue in the parser. You can replace such unwanted characters fore parser starts parsing the text. | ||||||||||||||||||||||||||
| IgnoreContentLengthHeaderForGzipResponse (Setting of JsonConfigurationParams) | IgnoreContentLengthHeaderForGzipResponse | |||||||||||||||||||||||||||
| ReplaceWith (Setting of JsonConfigurationParams) | ReplaceWith | String you like to replace with (Only valid when EnableCustomReplace option is turned on). If you added --regex or --regex-ic at the end of your SearchFor string then ReplaceWith can use special placeholders (i.e. $1, $2...) based on regular expression groups. For example you SearchFor=(\w+)(@\w+.com) to search for emails then to mask emails you can something like this for ReplaceWith = ****$2 (where $2 is domain part and $1 is before @) | ||||||||||||||||||||||||||
| SearchFor (Setting of JsonConfigurationParams) | SearchFor | String you like to search for (Only valid when EnableCustomReplace option is turned on). If you want to enable Regular Expression pattern search then add --regex or --regex-ic (for case-insensitive search) at the end of your search string (e.g. ORDER-\d+--regex OR ORDER-\d+--regex-ic (case-insensitive search) ) | ||||||||||||||||||||||||||
| 2D Array Settings | ||||||||||||||||||||||||||||
| ArrayTransColumnNameFilter (Setting of JsonConfigurationParams) | Array Transformation ColumnName Filter | Filter expression to use to extract column names for array transformation. | ||||||||||||||||||||||||||
| ArrayTransCustomColumns (Setting of JsonConfigurationParams) | Columns Names for Array Transformation | When you have 2D array but don't have column list specified in a separate array then use specify column names here. Use comma separated list (e.g col1,col2,col3 ). Column name Order must match value order. | ||||||||||||||||||||||||||
| ArrayTransEnableCustomColumns (Setting of JsonConfigurationParams) | Use Custom Columns for Array Transformation | When you have 2D array but don't have column list specified in a separate array then use this option (e.g. { arr: [[10,11],[21,22]] } ). If you selected Column less array or JSON Lines option then this property means Column Names coming from First Line of array. | ||||||||||||||||||||||||||
| ArrayTransformType (Setting of JsonConfigurationParams) | Array Transformation Type | Array Transformation you want to apply. Useful for case when you have 2-Dimensional arrays with rows/columns in separate arrays.
| ||||||||||||||||||||||||||
| ArrayTransRowValueFilter (Setting of JsonConfigurationParams) | Array Transformation Row Filter | Filter expression for row values (Not applicable for simple array transformation). | ||||||||||||||||||||||||||
| Cache Settings | ||||||||||||||||||||||||||||
| CacheEntryTtl (Setting of JsonConfigurationParams) | Cache entry lifetime, sec | This property indicates how many seconds data is kept in cache (in case caching is enabled) | ||||||||||||||||||||||||||
| CacheFileLocation (Setting of JsonConfigurationParams) | Cache file location | This property allows you to set cache file location (you have to set Cache persistence mode to Storage) | ||||||||||||||||||||||||||
| CacheStorage (Setting of JsonConfigurationParams) | Cache storage | Specifies cache storage - in-memory only or local data file
| ||||||||||||||||||||||||||
| CachingMode (Setting of JsonConfigurationParams) | Cache mode | Enables or disables cache usage for metadata / data
| ||||||||||||||||||||||||||
| Encoding / Culture Settings | ||||||||||||||||||||||||||||
| CharacterSet (Setting of JsonConfigurationParams) | CharacterSet | Character set for text (e.g. utf-8 ) | ||||||||||||||||||||||||||
| Encoding (Setting of JsonConfigurationParams) | Encoding | Encoding of source file
| ||||||||||||||||||||||||||
| Error Handling Settings | ||||||||||||||||||||||||||||
| ConvErrorHandlingMode (Setting of JsonConfigurationParams) | Error handling for data conversion | Determines whether some data conversion errors are ignored or stop query processing.
| ||||||||||||||||||||||||||
| Filter Settings | ||||||||||||||||||||||||||||
| ElementScopeSeparator (Setting of JsonConfigurationParams) | Child scope separator | Scope separator for nested properties (Child properties under selected filter). For example you may have $.contacts[*] as your filter and for each contact you may have nested node e.g AddressInfo / ZipInfo / Zip. By default column name will be AddressInfo.ZipInfo.Zip (Default separator is dot) but if you want column name like AddressInfo_ZipInfo_Zip in the output then change this property to any custom value (e.g. _). If you dont set this property then dot will be used. | ||||||||||||||||||||||||||
| FilterForParentColumnsWithArrayType (Setting of JsonConfigurationParams) | Filter for parent columns of array type | Set this option to true if you want to output parent columns which are of array type. By default any parent column which is an array is not included in output. See also FilterForParentColumnsWithArrayType property if you set this property | ||||||||||||||||||||||||||
| IncludeParentColumns (Setting of JsonConfigurationParams) | Include parent columns | If this option is enabled then parent properties of non-array type will be output along with regular row attributes. | ||||||||||||||||||||||||||
| IncludeParentColumnsWithArrayType (Setting of JsonConfigurationParams) | Include parent columns of array type | Set this option to true if you want to output parent columns which are array. By default any parent column which is an array is not included in output. See also FilterForParentColumnsWithArrayType property if you set this property | ||||||||||||||||||||||||||
| IncludeParentWhenChildNull (Setting of JsonConfigurationParams) | IncludeParentWhenChildNull | By default child and parent information is not included in the output if children not found for specified expression. For example if you want to extract all orders from all customers nodes then you can type $.Customers[*].Orders[*]. This will fetch all orders from all customers. By default customers records without orders wont be included in the output. If you want to include those customers where orders not found then check this option (Output null information for order attributes). This behavior is similar to LEFT OUTER JOIN in SQL (Left side is parent, right side is child). This option is ***resource intensive*** so only check if you really care about this behavior. | ||||||||||||||||||||||||||
| LevelSeparator (Setting of JsonConfigurationParams) | Parent scope separator | Property level separator used in generated property name (separator for outer properties - Above selected filter node). Use this if default separator is producing duplicate property name which is conflicting with existing name. | ||||||||||||||||||||||||||
| LevelsToScan (Setting of JsonConfigurationParams) | Maximum levels to scan | Specifies how many nested levels should be scanned. 0 means no limitations to scan. | ||||||||||||||||||||||||||
| ParentColumnPrefix (Setting of JsonConfigurationParams) | Parent prefix | Value of this parameter will be used as prefix of parent element. Empty means default prefix will be used. | ||||||||||||||||||||||||||
| ThrowErrorWhenNoMatch (Setting of JsonConfigurationParams) | Throw error on Filter no-matches | Determines whether error is thrown in case data table name element is missing from response or no data row is returned. | ||||||||||||||||||||||||||
| Log Settings | ||||||||||||||||||||||||||||
| LoggingLevel (Setting of JsonConfigurationParams) | Log level | Sets level of messages logged
| ||||||||||||||||||||||||||
| LogPath (Setting of JsonConfigurationParams) | Log file location | Sets location for the log file (empty value means no log written) | ||||||||||||||||||||||||||
| LogSize (Setting of JsonConfigurationParams) | Maximum log file size (Mb) | Sets limit for log file size (0 means no limit, old log entries will be discarded if size limit is reached) | ||||||||||||||||||||||||||
| GroupName (Setting of ColumnsMetadata) | Group name | |||||||||||||||||||||||||||
| OverrideMode (Setting of ColumnsMetadata) | OverrideMode | |||||||||||||||||||||||||||
| Metadata Settings | ||||||||||||||||||||||||||||
| MetaScanMode (Setting of JsonConfigurationParams) | Metadata Scan Mode / Multiplier | Metadata guess mode / multiplier to detect length by scanning rows defined in
| ||||||||||||||||||||||||||
| MetaTreatStringAsAscii (Setting of JsonConfigurationParams) | Treat string as ASCII | When this option is true, it detects all string values as DT_STR (Ascii) rather than DT_WSTR (Unicode) | ||||||||||||||||||||||||||
| RowsToScanForMetadata (Setting of JsonConfigurationParams) | Number of rows scanned to collect metadata | If metadata isn't defined for query the driver will try to scan some rows to guess fields parameters | ||||||||||||||||||||||||||
| TreatBlankBoolAsNull (Setting of JsonConfigurationParams) | Treat blank BOOLEAN value as NULL | |||||||||||||||||||||||||||
| TreatBlankDateAsNull (Setting of JsonConfigurationParams) | Treat blank DATE/TIME value as NULL | |||||||||||||||||||||||||||
| TreatBlankNumberAsNull (Setting of JsonConfigurationParams) | Treat blank NUMERIC value as NULL | |||||||||||||||||||||||||||
| Other Settings | ||||||||||||||||||||||||||||
| DateParseHandling (Setting of JsonConfigurationParams) | Date data type | Specifies how dates are parsed.
| ||||||||||||||||||||||||||
| EnableBigNumberHandling (Setting of JsonConfigurationParams) | Enable Big Number Handling | When this option is checked, sytem allows to parse large number (Higher than Int64) in JSON document (i.e. { id: 76000009353950433247780 } ). If you do not turn on this option then it may throw error if large number is encountered. This option is SLOW becuase it has to perform additional checks in order to avoid Large number overflow error so do not turn on unless you getting Large number error. | ||||||||||||||||||||||||||
| FloatParseHandling (Setting of JsonConfigurationParams) | FloatParseHandling | Specifies how decimal values are parsed when reading JSON. Change this setting to Decimal if you like to have large precision / scale.
| ||||||||||||||||||||||||||
| Recursive (Setting of JsonConfigurationParams) | Scan files recursively | Scan files recursively when you read from local files (i.e. include files from sub folders). | ||||||||||||||||||||||||||
| Output Settings | ||||||||||||||||||||||||||||
| DateFormatString (Setting of JsonConfigurationParams) | Date format | Specifies date format | ||||||||||||||||||||||||||
| DefaultRowLimit (Setting of JsonConfigurationParams) | Default data row limit (i.e. LIMIT / TOP clause) | Use this option if you like to limit number of rows fetched by any query. If you use LIMIT clause in the query then this option is ignored. | ||||||||||||||||||||||||||
| EnableArrayFlattening (Setting of JsonConfigurationParams) | Enable Array Flattening | Enables deep array flattening for selected filtered hierarchy. When you turn on this property it will flatten each property of each array item and expose as column (e.g. If you have Filter set as $.customers[*] and for each customer you have an array of Addresses then you may see output columns like Addresses.1.City, Addresses.1.State, Addresses.2.City, Addresses.2.State .... Addresses.N.City, Addresses.N.State). You can control how many array items you want to flatten by setting MaxArrayItemsToFlatten property. | ||||||||||||||||||||||||||
| EnablePivot (Setting of JsonConfigurationParams) | Enable Pivot | When this property is true then Column is converted to Row. Pivoted names will appear under Pivot_Name column and values will appear under Pivot_Value field. | ||||||||||||||||||||||||||
| EnableRawOutputMode (Setting of JsonConfigurationParams) | Enable Raw Document Output Mode | Output as raw JSON/XML document rather than parsing individual fields. This option is helpful if you have documents stored in a file and you want to pass them downstream as raw JSON string rather than parsing into columns. | ||||||||||||||||||||||||||
| EnableRawOutputModeSingleRow (Setting of JsonConfigurationParams) | Enable Raw Document - Single Row (Disable parser, allow any format) | Enable Raw Document Output Mode for any format (i.e. XML, Html, Text, Json). Unlike other option EnableRawOutputMode, this option doesn't invoke parser to extract documents by finding row terminator. It will source string as row value in single row/ single column. | ||||||||||||||||||||||||||
| ExcludeColumns (Setting of JsonConfigurationParams) | Excluded properties | List of comma separated element names which you want to exclude from output. Specify parent element name to exclude all child nodes. | ||||||||||||||||||||||||||
| FileCompressionType (Setting of JsonConfigurationParams) | FileCompressionType | Compression format for source file (e.g. gzip, zip)
| ||||||||||||||||||||||||||
| IndentOutput (Setting of JsonConfigurationParams) | Indent output | Indent JSON output to improve readability. | ||||||||||||||||||||||||||
| MaxArrayItemsToFlatten (Setting of JsonConfigurationParams) | Max Array Items To Flatten | Maximum number of array items to flatten. inner array flattening. Adjust this property to control how many columns being generated. This option is ignored if you set EnableArrayFlattening=false | ||||||||||||||||||||||||||
| RawOutputDataRowTemplate (Setting of JsonConfigurationParams) | RawOutputDataRowTemplate | |||||||||||||||||||||||||||
| RawOutputExtractMode (Setting of JsonConfigurationParams) | RawOutputExtractMode |
| ||||||||||||||||||||||||||
| RawOutputFilterExpr (Setting of JsonConfigurationParams) | RawOutputFilterExpr | Use this property to define one or multiple expressions (i.e. JsonPath,XPath,Regex) to extract information from source. This property is only valid when EnableRawOutputModeSingleRow=true and RawOutputDataRowTemplate is set. | ||||||||||||||||||||||||||
| SaveContentAsBinary (Setting of JsonConfigurationParams) | SaveContentAsBinary | |||||||||||||||||||||||||||
| Query Engine Temp Storage | ||||||||||||||||||||||||||||
| TempStorageMode (Setting of JsonConfigurationParams) | Intermediate results storage | Use [Disk] if number of resulting rows or row data size is large
| ||||||||||||||||||||||||||
| Request Settings | ||||||||||||||||||||||||||||
| DataPath (Setting of JsonConfigurationParams) | Data Source (URL or File Path) | Data source URL or File Path (e.g. http://abc.com/api/get --or-- c:\data\cust01.json --or-- c:\data\cust*.json ). If data source is file then wildcard pattern can be also used to read multiple files (e.g. c:\data\cust*.json will read all files that match wildcard) | ||||||||||||||||||||||||||
| Filter (Setting of JsonConfigurationParams) | Filter | Specifies expression to filter data. Usually this is Array path from which you like to get all records. Example: $.Customers[*] will get all customers from Customer Array from your document and $.Customers[*].Orders[*] will get all orders for all customers. | ||||||||||||||||||||||||||