{"id":14788,"date":"2021-09-02T16:58:38","date_gmt":"2021-09-02T20:58:38","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=14788"},"modified":"2021-09-03T16:24:29","modified_gmt":"2021-09-03T20:24:29","slug":"table-lookups-sortcl-compatible-jobs","status":"publish","type":"post","link":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/","title":{"rendered":"Table Lookups in SortCL-Compatible IRI Jobs"},"content":{"rendered":"
Introduction<\/strong><\/h6>\n

SortCL<\/a>, the IRI language of structured data definition and manipulation, has long had the ability to draw replacement values from external set files containing two or more columns of values. This capability is used for lookup transforms<\/a> in CoSort-powered Voracity ETL operations, FieldShield pseudonymization<\/a> and restoration functions, and random \/ valid-pair data selection<\/a> in RowGen test data synthesis jobs.<\/span>1<\/sup><\/a><\/span><\/p>\n

These set files are great for information that doesn’t change often. But because set files must be sorted by column contents, they can be cumbersome to use when rows need to be added — especially while the set file is open \/ in use.<\/p>\n

In addition, the contents of a set file often actually originate in a database. In those cases, an extra step (like running the IRI Workbench \u2018Set File from Column\u2019 wizard, or a SQL operation) had to be added to the job flow to extract values from a table into a set file.<\/p>\n

To address these drawbacks, the direct look-up of replacement values from existing database tables has been enabled. Lookups from a database table use the same syntax and structure for table lookups already in place for set files. This maintains functional consistency in SortCL jobs and provides access to more values (in multiple databases and set files) at once.<\/p>\n

Syntax<\/strong><\/h6>\n

SortCL field attribute syntax for accessing data in a set file has traditionally been:<\/p>\n

SET=\"<Set_Source<\/i>>\"[<[ Search List<\/i> ]>]\r\n\u00a0\u00a0\u00a0 [DEFAULT=\"string<\/i>\"]\r\n\u00a0\u00a0\u00a0 [ORDER=<Order Option<\/i>>]\r\n\u00a0\u00a0\u00a0 [SELECT=<Select Option<\/i>>]\r\n\u00a0\u00a0\u00a0 [SEARCH=<Search Option<\/i>>]<\/pre>\n
where the <Set Source><\/i> parameter signifies the path name of the set file. This parameter can also now be an ODBC table name and connection string, just like the one used in infile or outfile statements. The Search List parameter should reference a single field from one of the input sources in the case of table lookups.<\/p>\n
Your SortCL (or compatible) program will then look for a match between the value of this field and the lookup column in the database. If there is a match, then the value of the replacement column at this row will be used as the final value for the new field, which should have a different name than the field referenced from one of the input sources.<\/p>\n
The table columns used in the lookup are specified in a single additional language element to the initial implementation of the SET sub-attribute:\u00a0 LOOKUP=”<lvalue<\/i>>,<rvalue<\/i>>”.<\/p>\n
The parameter lvalue <\/i>is the name of the column in the table that holds the value to be looked up. This corresponds to the left-hand, or first, column in a set file. The rvalue <\/i>part corresponds to the right-hand column in a two column set file, and is whichever column holds the value to be returned as the replacement.<\/p>\n
As with traditional set file lookups, a default value should be specified if there is no match. The DEFAULT=\u201dstring\u201d syntax, where \u201cstring\u201d is the manually specified default value, is used. There should be no commas between any of the set file parameters.<\/p>\n
Putting it all together, a possible example of the syntax for a table lookup might be:<\/p>\n
SET=”new_schema.info;DSN=New MySQL;” [ACCOUNT_NUMBER] LOOKUP=”ACCOUNTNUM,PHONENUM”<\/p>\n
This should be a parameter within a SortCL field definition. The \u201cinfo\u201d table should have columns named ACCOUNTNUM and PHONENUM in this case.<\/p>\n
How might these new set lookups be used in production? Consider these IRI job script examples:<\/p>\n
Examples<\/strong><\/h6>\n
Example 1<\/i><\/b>: Pseudonymize data using replacement values from a database.<\/i><\/p>\n
This FieldShield job looks up values from the \u201cid\u201d column in the table named \u201clookuptable\u201d accessed through the DSN \u201cMangled\u201d. If the ID field is the same in the input (a text file) as in the ID column of the referenced database table, then all fields in the output (also a text file) will be replaced with fake but realistic replacement values also from the same referenced table. If there is no match, the default values specified in the script will be output instead.<\/p>\n
\/INFILE=sensitiveData.txt\r\n\u00a0\u00a0\u00a0 \/PROCESS=DELIMITED\r\n\u00a0\u00a0\u00a0 \/INCOLLECT=200 # limit to 200 records\r\n\u00a0\u00a0\u00a0 \/FIELD=(ID, TYPE=ASCII, POSITION=1, SEPARATOR=\"|\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(NAME, TYPE=ASCII, POSITION=2, SEPARATOR=\"|\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(SSN, TYPE=ASCII, POSITION=3, SEPARATOR=\"|\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(ADDRESS, TYPE=ASCII, POSITION=4, SEPARATOR=\"|\")\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=pseudonymizedData.txt\r\n\u00a0\u00a0\u00a0 \/PROCESS=RECORD\r\n\u00a0\u00a0\u00a0 \/FIELD=(PSEUDO_ID, TYPE=ASCII, POSITION=1, SEPARATOR=\"|\", SET=\"lookuptable;DSN=Mangled;\" [ID] DEFAULT=\u201d0123456789\u201d LOOKUP=\"id,fakeid\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(PSEUDO_NAME, TYPE=ASCII, POSITION=2, SEPARATOR=\"|\", SET=\"lookuptable;DSN=Mangled;\" [ID] DEFAULT=\u201dJohn\u201d LOOKUP=\"id,fakename\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(PSEUDO_SSN, TYPE=ASCII, POSITION=3, SEPARATOR=\"|\", SET=\"lookuptable;DSN=Mangled;\" [ID] DEFAULT=\u201d555-55-5555\u201d LOOKUP=\"id,fakessn\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(PSEUDO_ADDRESS, TYPE=ASCII, POSITION=4, SEPARATOR=\"|\", SET=\"lookuptable;DSN=Mangled;\" [ID] DEFAULT=\u201d583 West Ridge Rd\u201d LOOKUP=\"id,fakeaddress\")<\/pre>\n
Example 2<\/i><\/b>: Pseudonymize three columns from a database table using replacement values from a different database, and encrypt the remaining column.<\/i><\/p>\n
This script does a lookup based on the ID field taken from the database table named \u201cinputTable\u201d, looking at the \u201cid\u201d column from the table named \u201clookuptable\u201d accessed through the DSN \u201cMangled\u201d. The matching values in the \u201cfakeid\u201d, \u201cfakename\u201d, \u201cfakessn\u201d, and \u201cfakeaddress\u201d columns will be taken if there is a match from the input data ID field to the \u201cid\u201d column in the table. If there is no match, default values will be output instead.<\/p>\n
The output will be sent to a separate target table. The output could also be sent to the same table as the input, which would mask the data in place.<\/p>\n
\/INFILE=\u201dinputTable;DSN=Mangled;\u201d\r\n\u00a0\u00a0\u00a0 \/PROCESS=ODBC\r\n\u00a0\u00a0\u00a0 \/FIELD=(ID, TYPE=ASCII, POSITION=1, SEPARATOR=\"|\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(NAME, TYPE=ASCII, POSITION=2, SEPARATOR=\"|\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(SSN, TYPE=ASCII, POSITION=3, SEPARATOR=\"|\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(ADDRESS, TYPE=ASCII, POSITION=4, SEPARATOR=\"|\")\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=\u201doutputTable;DSN=Mangled;\u201d\r\n\u00a0\u00a0\u00a0 \/PROCESS=ODBC\r\n\u00a0\u00a0\u00a0 \/FIELD=(PSEUDO_ID, TYPE=ASCII, POSITION=1, SEPARATOR=\"|\", SET=\"lookuptable;DSN=Mangled;\" [ID] DEFAULT=\u201d0123456789\u201d LOOKUP=\"id,fakeid\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(PSEUDO_NAME, TYPE=ASCII, POSITION=2, SEPARATOR=\"|\", SET=\"lookuptable;DSN=Mangled;\" [ID] DEFAULT=\u201dJohn\u201d LOOKUP=\"id,fakename\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(ENCRYPT_SSN=enc_fp_aes256_alphanum(SSN,\u201dEPWD:p4PagGZq9k7JFaj6\/J1\/JQ==\u201d, TYPE=ASCII, POSITION=3, SEPARATOR=\"|\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(PSEUDO_ADDRESS, TYPE=ASCII, POSITION=4, SEPARATOR=\"|\", SET=\"lookuptable;DSN=Mangled;\" [ID] DEFAULT=\u201d583 West Ridge Rd\u201d LOOKUP=\"id,fakeaddress\")<\/pre>\n
Example 3<\/i><\/b>: Protecting Personally Identifiable Information (PII) using realistic replacements from a diverse set of masking methods.<\/i><\/p>\n
The input file contains PII in several columns (fields). If there is a match between the first name field in the input CSV file and the \u201cFIRST_NAME\u201d column in the \u201cNAMES\u201d table, then a replacement last name will be output from the \u201cLAST_NAME\u201d column in that same table. The last names differ in the \u201cNAMES\u201d table from the personal data itself.<\/p>\n
\/INFILES=personalData.csv\r\n\u00a0\u00a0\u00a0 \/PROCESS=CSV\r\n\u00a0\u00a0\u00a0 \/ALIAS=PERSONALDATA_CSV\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIRST_NAME, TYPE=ASCII, POSITION=1, SEPARATOR=\",\", FRAME=\"\\\"\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(LAST_NAME, TYPE=ASCII, POSITION=2, SEPARATOR=\",\", FRAME=\"\\\"\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(SSN, TYPE=ASCII, POSITION=3, SEPARATOR=\",\", FRAME=\"\\\"\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(BIRTH_DATE, TYPE=AMERICAN_DATE, POSITION=4, SEPARATOR=\",\", FRAME=\"\\\"\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(ADDRESS, TYPE=ASCII, POSITION=5, SEPARATOR=\",\", FRAME=\"\\\"\")\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=maskedData.csv\r\n\u00a0\u00a0\u00a0 \/PROCESS=RECORD\r\n\u00a0\u00a0\u00a0 \/FIELD=(FIRST_NAME, TYPE=ASCII, POSITION=1, SEPARATOR=\",\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(LAST_NAME_DB_SET, TYPE=ASCII, POSITION=2, SEPARATOR=\",\", SET=\"NAMES;DSN=mangled;\" [FIRST_NAME] LOOKUP=\"FNAME,LNAME\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(SSNENC=enc_fp_aes256_alphanum(SSN), TYPE=ASCII, POSITION=3, SEPARATOR=\",\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(BIRTH_DATEPLUS=BIRTH_DATE + 30, TYPE=AMERICAN_DATE, POSITION=4, SEPARATOR=\",\")\u00a0\r\n\u00a0\u00a0\u00a0 \/FIELD=(ADDRESSSET, TYPE=ASCII, POSITION=5, SEPARATOR=\",\", SET=\"C:\/IRI\/cosort100\/sets\/addresses.set\" SELECT=ANY)<\/pre>\n
The same job script, outline an mapping diagram, along with the original name table is shown in my IRI Workbench<\/a> IDE, built on Eclipse, below:<\/p>\n
\"\"<\/a><\/p>\n
Example 4<\/i><\/b>: Generating referentially correct test data with IRI RowGen<\/i><\/p>\n
Consider a database table, or in other potential cases multiple tables, that hold data that correspond to a certain date. With IRI RowGen and table lookup functionality, it is possible to take a selection of dates (from a set file or randomly generated) and add on more fields in the output that correspond to realistic values based on the date input provided.<\/p>\n
In this example, all of the corresponding data from the date is held in the lookup table shown below (though it could be taken from any number of tables). The table has a year\u2019s worth of dates and the corresponding related values:<\/p>\n
\"\"<\/a><\/p>\n
From the set file in the DATE input field, 3 dates are selected that are within the range of dates included in the table.<\/p>\n
The set file includes three entries: 2019-10-11, 2019-11-11, and 2019-12-11.<\/p>\n
\/INFILE=random_file_placeholder\r\n\u00a0\u00a0\u00a0 \/PROCESS=RANDOM\r\n\u00a0\u00a0\u00a0 \/INCOLLECT=3\r\n\u00a0\u00a0\u00a0 \/FIELD=(DATE, TYPE=ALPHA_DIGIT, POSITION=1, SEPARATOR=\",\", SET=\"C:\/Users\/Devon\/Downloads\/dates.set\" SELECT=ALL)\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=testPriceData.xml\r\n\u00a0\u00a0\u00a0 \/PROCESS=XML\r\n\u00a0\u00a0\u00a0 \/FIELD=(DATE, TYPE=ALPHA_DIGIT, POSITION=1, SEPARATOR=\",\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(OPEN, TYPE=ALPHA_DIGIT, POSITION=2, SEPARATOR=\",\", SET=\"new_schema2.pricedata;DSN=New MySQL;\" [DATE] DEFAULT=\"170\" LOOKUP=\"Date,Open\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(HIGH, TYPE=ALPHA_DIGIT, POSITION=3, SEPARATOR=\",\", SET=\"new_schema2.pricedata;DSN=New MySQL;\" [DATE] DEFAULT=\"171\" LOOKUP=\"Date,High\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(LOW, TYPE=ALPHA_DIGIT, POSITION=4, SEPARATOR=\",\",SET=\"new_schema2.pricedata;DSN=New MySQL;\" [DATE] DEFAULT=\"169\" LOOKUP=\"Date,Low\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(CLOSE, TYPE=ALPHA_DIGIT, POSITION=5, SEPARATOR=\",\",SET=\"new_schema2.pricedata;DSN=New MySQL;\" [DATE] DEFAULT=\"170.5\" LOOKUP=\"Date,Close\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(ADJ_CLOSE, TYPE=ALPHA_DIGIT, POSITION=6, SEPARATOR=\",\",SET=\"new_schema2.pricedata;DSN=New MySQL;\" [DATE] DEFAULT=\"170.5\" LOOKUP=\"Date,Adj_Close\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(VOLUME, TYPE=ALPHA_DIGIT, POSITION=7, SEPARATOR=\",\",SET=\"new_schema2.pricedata;DSN=New MySQL;\" [DATE] DEFAULT=\"523210182\" LOOKUP=\"Date,Volume\")<\/pre>\n
The output from this example is a standard XML file containing the lookup values:<\/p>\n
\"\"<\/a><\/p>\n
Example 5<\/i><\/b>: Performing a Lookup Transform in an IRI Voracity ETL and Reporting Job<\/i><\/p>\n
In this example, we have a CSV file containing account numbers and past due amounts for several customers. A table lookup will be used in two fields in the output to get additional matching information from a master customer fact table in MySQL, with that table serving as the master customer table.<\/p>\n
The master table does not have information about the amount due and contains many more customers than the input file which only shows delinquent customer accounts. This looks up\u00a0 the name and phone number from the table based on the account number and outputs to an .XLSX spreadsheet in a handy report format with customer contact information.<\/p>\n
\"\"<\/a>Input CSV<\/em><\/p>\n
\"\"<\/a>Snippet of the Master Customer Table to be looked up against<\/em><\/p>\n
\/INFILE=C:\/Users\/Devon\/Downloads\/accountnumsandamountDue.csv\r\n\u00a0\u00a0\u00a0 \/PROCESS=CSV\r\n\u00a0\u00a0\u00a0 \/ALIAS=accountnumsandamountDue\r\n\u00a0\u00a0\u00a0 \/FIELD=(ACCOUNT_NUMBER, TYPE=ASCII, POSITION=1, SEPARATOR=\",\", FRAME=\"\\\"\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(AMOUNT_DUE, TYPE=ASCII, POSITION=2, SEPARATOR=\",\", FRAME=\"\\\"\")\r\n\r\n\/REPORT\r\n\r\n\/OUTFILE=\"'Past Due',HEADER;report.xlsx\"\r\n\u00a0\u00a0\u00a0 \/PROCESS=XLSX\r\n\u00a0\u00a0\u00a0 \/FIELD=(ACCOUNT_NUMBER, TYPE=ASCII, POSITION=1, SEPARATOR=\"\\t\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(LOOKUP_NAME,TYPE=ASCII,POSITION=2, SEPARATOR=\"\\t\",SET=\"new_schema2.info;DSN=New MySQL;\" [ACCOUNT_NUMBER] LOOKUP=\"ACCOUNTNUM,NAME\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(LOOKUP_PHONE,TYPE=ASCII,POSITION=3, SEPARATOR=\"\\t\",SET=\"new_schema2.info;DSN=New MySQL;\" [ACCOUNT_NUMBER] LOOKUP=\"ACCOUNTNUM,PHONENUM\")\r\n\u00a0\u00a0\u00a0 \/FIELD=(AMOUNT_DUE, TYPE=ASCII, POSITION=4, SEPARATOR=\"\\t\")<\/pre>\n
\"\"<\/a>Output \u201cPast Due\u201d report<\/em><\/p>\n
IRI Workbench Support<\/strong><\/h6>\n
Database table lookups may be set up as a rule from the New Field Rule Wizard. This type of rule is referred to as a \u201cTable Lookup\u201d under Generation Rules, but it can be used in multiple sources or targets as a field rule in other jobs.<\/p>\n
\"\"<\/a><\/p>\n
When selecting a table lookup as a rule, a connection profile must be either already set up or created when prompted in order to access the database tables and column names to choose from.<\/p>\n
\"\"<\/a><\/p>\n
From there, select the table, lookup column, and replacement value column to use for the lookup. Now this table lookup may be set as a rule to be applied based on matchers.<\/p>\n
Sets may be modified from the Set:Table Lookup value transformation type within the field editor dialog.<\/p>\n
\"\"<\/a><\/p>\n
This is not needed if a table lookup field rule has already been set up and applied. However, this dialog allows for manual editing of table lookup components such as DSN, table, search field, lookup column, and result column. A default value may also be specified here.<\/p>\n
Summary<\/strong><\/h6>\n
Set lookups now have a new possible source in SortCL that can greatly expand and ease obtaining the data needed for a lookup. This is useful in masking or data generation operations to provide realistic replacement values that preserve relationships.<\/p>\n
In the future, sets may be expanded to include even more data sources. Contact your IRI representative<\/a> for more information.<\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction SortCL, the IRI language of structured data definition and manipulation, has long had the ability to draw replacement values from external set files containing two or more columns of values. This capability is used for lookup transforms in CoSort-powered Voracity ETL operations, FieldShield pseudonymization and restoration functions, and random \/ valid-pair data selection in<\/p>\n
Read More<\/a><\/div>\n","protected":false},"author":119,"featured_media":14804,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[8,776,91,29,3],"tags":[519,546,520,526,1204,850,22,1519],"class_list":["post-14788","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-protection","category-etl","category-iri-workbench","category-test-data","category-vldb-operations","tag-database","tag-iri-cosort","tag-iri-fieldshield","tag-iri-rowgen","tag-iri-sortcl","tag-iri-workbench","tag-pseudonymization","tag-table-lookup"],"acf":[],"yoast_head":"\nTable Lookups in SortCL-Compatible IRI Jobs - IRI<\/title>\n<meta name=\"description\" content=\"This capability is used for lookup transforms in CoSort-powered Voracity ETL operations, FieldShield pseudonymization and restoration functions, and\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Table Lookups in SortCL-Compatible IRI Jobs\" \/>\n<meta property=\"og:description\" content=\"This capability is used for lookup transforms in CoSort-powered Voracity ETL operations, FieldShield pseudonymization and restoration functions, and\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/\" \/>\n<meta property=\"og:site_name\" content=\"IRI\" \/>\n<meta property=\"article:published_time\" content=\"2021-09-02T20:58:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-09-03T20:24:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/09\/db-table-lookup-newLookupRuleScreen.png\" \/>\n\t<meta property=\"og:image:width\" content=\"511\" \/>\n\t<meta property=\"og:image:height\" content=\"443\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Devon Kozenieski\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Devon Kozenieski\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/\"},\"author\":{\"name\":\"Devon Kozenieski\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1\"},\"headline\":\"Table Lookups in SortCL-Compatible IRI Jobs\",\"datePublished\":\"2021-09-02T20:58:38+00:00\",\"dateModified\":\"2021-09-03T20:24:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/\"},\"wordCount\":1486,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/09\/db-table-lookup-newLookupRuleScreen.png\",\"keywords\":[\"database\",\"IRI CoSort\",\"IRI FieldShield\",\"IRI RowGen\",\"IRI SortCL\",\"IRI Workbench\",\"pseudonymization\",\"table lookup\"],\"articleSection\":[\"Data Masking\/Protection\",\"ETL\",\"IRI Workbench\",\"Test Data\",\"VLDB\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/\",\"url\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/\",\"name\":\"Table Lookups in SortCL-Compatible IRI Jobs - IRI\",\"isPartOf\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/09\/db-table-lookup-newLookupRuleScreen.png\",\"datePublished\":\"2021-09-02T20:58:38+00:00\",\"dateModified\":\"2021-09-03T20:24:29+00:00\",\"description\":\"This capability is used for lookup transforms in CoSort-powered Voracity ETL operations, FieldShield pseudonymization and restoration functions, and\",\"breadcrumb\":{\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#primaryimage\",\"url\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/09\/db-table-lookup-newLookupRuleScreen.png\",\"contentUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/09\/db-table-lookup-newLookupRuleScreen.png\",\"width\":511,\"height\":443},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.iri.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Table Lookups in SortCL-Compatible IRI Jobs\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.iri.com\/blog\/#website\",\"url\":\"https:\/\/www.iri.com\/blog\/\",\"name\":\"IRI\",\"description\":\"Total Data Management Blog\",\"publisher\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.iri.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.iri.com\/blog\/#organization\",\"name\":\"IRI\",\"url\":\"https:\/\/www.iri.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png\",\"contentUrl\":\"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png\",\"width\":750,\"height\":206,\"caption\":\"IRI\"},\"image\":{\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1\",\"name\":\"Devon Kozenieski\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.iri.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e4c421588c1a85dd9a76146fe15528f7?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/e4c421588c1a85dd9a76146fe15528f7?s=96&d=blank&r=g\",\"caption\":\"Devon Kozenieski\"},\"url\":\"https:\/\/www.iri.com\/blog\/author\/devonk\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Table Lookups in SortCL-Compatible IRI Jobs - IRI","description":"This capability is used for lookup transforms in CoSort-powered Voracity ETL operations, FieldShield pseudonymization and restoration functions, and","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/","og_locale":"en_US","og_type":"article","og_title":"Table Lookups in SortCL-Compatible IRI Jobs","og_description":"This capability is used for lookup transforms in CoSort-powered Voracity ETL operations, FieldShield pseudonymization and restoration functions, and","og_url":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/","og_site_name":"IRI","article_published_time":"2021-09-02T20:58:38+00:00","article_modified_time":"2021-09-03T20:24:29+00:00","og_image":[{"width":511,"height":443,"url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/09\/db-table-lookup-newLookupRuleScreen.png","type":"image\/png"}],"author":"Devon Kozenieski","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Devon Kozenieski","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#article","isPartOf":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/"},"author":{"name":"Devon Kozenieski","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1"},"headline":"Table Lookups in SortCL-Compatible IRI Jobs","datePublished":"2021-09-02T20:58:38+00:00","dateModified":"2021-09-03T20:24:29+00:00","mainEntityOfPage":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/"},"wordCount":1486,"commentCount":0,"publisher":{"@id":"https:\/\/www.iri.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#primaryimage"},"thumbnailUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/09\/db-table-lookup-newLookupRuleScreen.png","keywords":["database","IRI CoSort","IRI FieldShield","IRI RowGen","IRI SortCL","IRI Workbench","pseudonymization","table lookup"],"articleSection":["Data Masking\/Protection","ETL","IRI Workbench","Test Data","VLDB"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/","url":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/","name":"Table Lookups in SortCL-Compatible IRI Jobs - IRI","isPartOf":{"@id":"https:\/\/www.iri.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#primaryimage"},"image":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#primaryimage"},"thumbnailUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/09\/db-table-lookup-newLookupRuleScreen.png","datePublished":"2021-09-02T20:58:38+00:00","dateModified":"2021-09-03T20:24:29+00:00","description":"This capability is used for lookup transforms in CoSort-powered Voracity ETL operations, FieldShield pseudonymization and restoration functions, and","breadcrumb":{"@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#primaryimage","url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/09\/db-table-lookup-newLookupRuleScreen.png","contentUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/09\/db-table-lookup-newLookupRuleScreen.png","width":511,"height":443},{"@type":"BreadcrumbList","@id":"https:\/\/www.iri.com\/blog\/vldb-operations\/table-lookups-sortcl-compatible-jobs\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.iri.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Table Lookups in SortCL-Compatible IRI Jobs"}]},{"@type":"WebSite","@id":"https:\/\/www.iri.com\/blog\/#website","url":"https:\/\/www.iri.com\/blog\/","name":"IRI","description":"Total Data Management Blog","publisher":{"@id":"https:\/\/www.iri.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.iri.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.iri.com\/blog\/#organization","name":"IRI","url":"https:\/\/www.iri.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png","contentUrl":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2019\/02\/iri-logo-total-data-management-small-1.png","width":750,"height":206,"caption":"IRI"},"image":{"@id":"https:\/\/www.iri.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/de972c035aaeecfc40a3ae2ea5ff7ba1","name":"Devon Kozenieski","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.iri.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/e4c421588c1a85dd9a76146fe15528f7?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e4c421588c1a85dd9a76146fe15528f7?s=96&d=blank&r=g","caption":"Devon Kozenieski"},"url":"https:\/\/www.iri.com\/blog\/author\/devonk\/"}]}},"jetpack_featured_media_url":"https:\/\/www.iri.com\/blog\/wp-content\/uploads\/2021\/09\/db-table-lookup-newLookupRuleScreen.png","_links":{"self":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/14788"}],"collection":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/users\/119"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/comments?post=14788"}],"version-history":[{"count":10,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/14788\/revisions"}],"predecessor-version":[{"id":14792,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/posts\/14788\/revisions\/14792"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/media\/14804"}],"wp:attachment":[{"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/media?parent=14788"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/categories?post=14788"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iri.com\/blog\/wp-json\/wp\/v2\/tags?post=14788"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}