AMS360 Data Lake API is used to extract data from a database using an API instead of an SQL query via a VPN Solution.
There are two main flows that need to be executed to extract data from the API. Firstly, authorization is required to access the API. To get authorization, a Bearer Token is required. Secondly, the actual flow to extract the data you need from the Data Lake.
Flow 1: Authorization/Bearer Token
In order to extract data from and API, a Bearer Token is required. The flow in the image below illustrates how to get a Bearer Token.
The image below shows the Calculator step before the HTTP Service Destination step. It references a “grant_type”, a “client_id” and a “client_secret” (provided by Vertafore) which are stored in a solution parameter.
The image below shows the HTTP Service Destination step with the URL and configuration used to get a Bearer Token. There is a connection to an AMS360 sandbox that contains a cookie that is required to establish a connection.
The image below shows the Calculator step after the HTTP Service Destination step. It references a deletion of the “grant_type”, “client_id” and “client_secret”. In addition, it references the Bearer access token.
Illustrated below is the Cache Destination Step. It is used to store a record temporarily in a cache. Provide the Key to be used and set the expiry of the cache (in seconds).
The flow must be executed to get the Bearer Token and store it in the Cache Destination Step.
Flow 2: Extracting Data
The image below illustrates the flow used to extract data from the API. A Cache Lookup Mapper step is used to retrieve Bearer Token. The HTTP Source Step is used within a Subflow to extract data from an API. This step is used because it has a pagination option which will be used to extract data.
Configuring the HTTP Service Source step
The image below shows the HTTP Service Source Step.
URL
The URL of the HTTP Service. The URL is shown in the image below and has the following parts:
1. Table – The Table that contains the data. This can be changed to use the required table. Below is a list of the available tables:
"afw_125natureofbus",
"afw_125uwsignature",
"afw_126sclaimsmade",
"afw_126scontractor",
"afw_126scoverage",
"afw_126shazard",
"afw_126spco",
"afw_126spcoquestion",
"afw_127coverage1",
"afw_127driver",
"afw_127underwriting",
"afw_127vehicle",
"afw_128businessinfo",
"afw_128covautosymbol",
"afw_128dealersdamage",
"afw_128garageoperation",
"afw_128garkeepers",
"afw_128storageinfo",
"afw_130inclexcl",
"afw_130policy",
"afw_130rating",
"afw_130submit",
"afw_131saddexposure",
"afw_131scoverage",
"afw_131scus",
"afw_131sglinfo",
"afw_131slocation",
"afw_131spolicy",
"afw_131sunderlyingpolicy",
"afw_131svehicle",
"afw_132authority",
"afw_132commodities",
"afw_132coverage",
"afw_132equipment",
"afw_132receipts",
"afw_132regulation",
"afw_132terminal",
"afw_132trailerinterchange",
"afw_140premiseinfo",
"afw_140subofins",
"afw_140valuerpt",
"afw_141classification",
"afw_141classification2",
"afw_141control",
"afw_141depositoryinfo",
"afw_141employee",
"afw_141erisainfo",
"afw_141erisaplan",
"afw_141generalinfo",
"afw_141messenger",
"afw_141messengerprotection",
"afw_141money",
"afw_141property",
"afw_141rating",
"afw_141safeprotection",
"afw_141vault",
"afw_143fobgeninformk",
"afw_143interesttype",
"afw_143mtccommodity",
"afw_143mtclegalliability",
"afw_143mtcoperation",
"afw_143mtcstatefiling",
"afw_143terminal",
"afw_143transconveyance",
"afw_143transoperation",
"afw_144glass",
"afw_144sign",
"afw_145account",
"afw_145bldgconstruction",
"afw_145location",
"afw_145papers",
"afw_145receivables",
"afw_145recordlocation",
"afw_145safeprotection",
"afw_145vault",
"afw_146equipfloater",
"afw_146equipsummary",
"afw_146locations",
"afw_146schedequip",
"afw_146storage",
"afw_146unschedequip",
"afw_147builderoperation",
"afw_147jobvalue",
"afw_147rigtranssecurity",
"afw_147specificjob",
"afw_148mediainfo",
"afw_148schedule",
"afw_148underwriting",
"afw_addotherinterest",
"afw_address",
"afw_agency",
"afw_agencyaddr",
"afw_agencychecklist",
"afw_agencydirectbilldeferredglaccount",
"afw_agencylicense",
"afw_agencynotation",
"afw_agencyxreftype",
"afw_agent",
"afw_annualpol",
"afw_applicant",
"afw_aptinsurer",
"afw_attachment",
"afw_authinfo",
"afw_autocert",
"afw_balancejournalentrycollection",
"afw_bankaccount",
"afw_bankreccleared",
"afw_bankrecstatement",
"afw_basicpolinfo",
"afw_benefitcarrier",
"afw_benefitcustomer",
"afw_benefitemployee",
"afw_benefitlobplan",
"afw_benefitpolicytran",
"afw_billingheader",
"afw_binder",
"afw_binderaoi",
"afw_boat",
"afw_boatengine",
"afw_boatequipment",
"afw_boatexperience",
"afw_boatoperator",
"afw_boatsummary",
"afw_boattrailer",
"afw_broker",
"afw_building",
"afw_businessunitaccess",
"afw_businessunitaccesskey",
"afw_businessunithierarchy",
"afw_busunitbrokerfilter",
"afw_busunitcompanyfilter",
"afw_busunitemployeefilter",
"afw_busunitvendorfilter",
"afw_cashdisbursment",
"afw_cashreceiptdeposit",
"afw_cashreceiptdetail",
"afw_cashreceiptheader",
"afw_cbuilding",
"afw_certholderinfo",
"afw_certliabprop",
"afw_cform",
"afw_claim",
"afw_claimcontact",
"afw_claiminjured",
"afw_claimpayment",
"afw_claimpropdamage",
"afw_claimremark",
"afw_claimriskinfo",
"afw_clocation",
"afw_cnamedinsured",
"afw_cnamedinsuredphonemap",
"afw_coinsured",
"afw_coinsuredphonemap",
"afw_collectionletterdetail",
"afw_commaddotherint",
"afw_commissionie",
"afw_company",
"afw_companyaddress",
"afw_companybilloptions",
"afw_companyplan",
"afw_compspecanswer",
"afw_compspecquesthead",
"afw_compspecquestion",
"afw_constant",
"afw_conviction",
"afw_coverage",
"afw_coveragecode",
"afw_coveragehome",
"afw_covoption",
"afw_cprem",
"afw_cpremtotal",
"afw_custaddpersonnel",
"afw_custbenefitinfo",
"afw_custcertholder",
"afw_custcontact",
"afw_custcontactresp",
"afw_custlosshist",
"afw_customer",
"afw_customeremployeesecurity",
"afw_customerrelationship",
"afw_custxref",
"afw_defaultagencycommission",
"afw_defaultcommission",
"afw_dependent",
"afw_directbilldownloaddetail",
"afw_directbilldownloadheader",
"afw_directbillentrycommission",
"afw_directbillentrycommissionbuabreakout",
"afw_directbillentrydetail",
"afw_directbillentryheader",
"afw_directbillstmtautorec",
"afw_directbillstmtdetail",
"afw_directbillstmtheader",
"afw_docattachment",
"afw_docrouting",
"afw_driveothercar",
"afw_driver",
"afw_emplicense",
"afw_employee",
"afw_employer",
"afw_equineage",
"afw_equinebreed",
"afw_equinecovuse",
"afw_equineeligiblecov",
"afw_equineoverage",
"afw_equineplan",
"afw_equinerate",
"afw_equinesex",
"afw_equinestatetax",
"afw_equineuse",
"afw_equineuseage",
"afw_equineusesex",
"afw_evidenceofprop",
"afw_evidenceofpropaoi",
"afw_exboatcoverage",
"afw_excesscert",
"afw_factor",
"afw_farmcategory",
"afw_farmexclprop",
"afw_farmgl",
"afw_farmitem",
"afw_farmpiuw",
"afw_farmpremiseinfo",
"afw_farmpropuw",
"afw_farmranch",
"afw_farmsubofins",
"afw_farmuw",
"afw_faxusersetup",
"afw_faxvendorsetup",
"afw_filing",
"afw_floodlocation",
"afw_floodrating",
"afw_floodsectionone",
"afw_floodsectiontwo",
"afw_floodtotal",
"afw_form",
"afw_formno",
"afw_formnocat",
"afw_formtype",
"afw_garage",
"afw_genbldgsubject",
"afw_generaljournal",
"afw_generaljournalrelation",
"afw_generalledgeraccounts",
"afw_generalledgerbranch",
"afw_generalledgerbranchdept",
"afw_generalledgerbudgetdetail",
"afw_generalledgerbudgetdivdept",
"afw_generalledgerbudgetheader",
"afw_generalledgerdepartment",
"afw_generalledgerdeptgroup",
"afw_generalledgerdivbranch",
"afw_generalledgerdivision",
"afw_generalledgerfiscalyearend",
"afw_generalledgergroup",
"afw_generalledgerinactiveaccountsfordiv",
"afw_generalledgerperiod",
"afw_generalledgersystemaccount",
"afw_generalledgertransaction",
"afw_glcert",
"afw_healthcoverage",
"afw_healthmember",
"afw_healthprem",
"afw_hiredborrowed",
"afw_homefeature",
"afw_homerating",
"afw_homereplacement",
"afw_horse",
"afw_horseplanlob",
"afw_invoice",
"afw_invoicebillingevent",
"afw_invoicebillto",
"afw_invoicecollection",
"afw_invoicecommission",
"afw_invoicecommissionbuabreakout",
"afw_invoicecompanypremium",
"afw_invoicecustpremium",
"afw_invoicemessage",
"afw_invoicesplitallocation",
"afw_invoicesplittemplate",
"afw_invoicetransaction",
"afw_invoicetransactionbuabreakout",
"afw_journalmemo",
"afw_leadlist",
"afw_leadlistdef",
"afw_leadlistdefdetail",
"afw_leadlistdefelement",
"afw_leadlistintcust",
"afw_lifebeneficiary",
"afw_lifecoverage",
"afw_lifeotherinsurance",
"afw_lifeowner",
"afw_lineofbusiness",
"afw_lobsetup",
"afw_location",
"afw_logicaltable",
"afw_lossdriver",
"afw_lossgenliab",
"afw_losshistory",
"afw_lossvehicle",
"afw_masteragent",
"afw_mobilehome",
"afw_name",
"afw_nonowned",
"afw_notes",
"afw_onaccountinvoice",
"afw_othercert",
"afw_otherinsurance",
"afw_payee",
"afw_paymentplan",
"afw_paymentplandistribution",
"afw_paymentplanfee",
"afw_paymentplanrounding",
"afw_paystatementdetail",
"afw_paystatementheader",
"afw_personalumbrella",
"afw_persumbrellarating",
"afw_phonenumber",
"afw_physician",
"afw_polcontact",
"afw_polcontactphonemap",
"afw_policybillingaddress",
"afw_policychange",
"afw_policychangeaoi",
"afw_policycompanypremium",
"afw_policyconvertdetail",
"afw_policycustpremium",
"afw_policypersonnel",
"afw_policypersonnelperiods",
"afw_policysubcustomer",
"afw_policytranpremium",
"afw_policytranpremiumbuabreakout",
"afw_policytransaction",
"afw_polumbrella",
"afw_pproducer",
"afw_prcode",
"afw_prevaddr",
"afw_printedcheck",
"afw_priorcarrier",
"afw_profileanswer",
"afw_profilequestion",
"afw_ratedate",
"afw_recentcustomer",
"afw_record",
"afw_recurringitem",
"afw_refgroup",
"afw_relationship",
"afw_relationshiptype",
"afw_remark",
"afw_renewallistcc",
"afw_renewallistdetail",
"afw_renewallistheader",
"afw_renewallistpolicy",
"afw_serviceagreement",
"afw_serviceagreementpolicies",
"afw_setupbillingtran",
"afw_snowmobile",
"afw_specbuilding",
"afw_speclocation",
"afw_specrisk",
"afw_specriskanswer",
"afw_specunderwritinganswer",
"afw_sppaddinfo",
"afw_sppitem",
"afw_sppsummary",
"afw_submission",
"afw_submissiongroup",
"afw_suspense",
"afw_targetlistbpol",
"afw_targetlistcustomer",
"afw_targetlistlob",
"afw_targetlistlobcoverage",
"afw_targetlistprofile",
"afw_targetlistxdate",
"afw_tblrelationship",
"afw_transaction",
"afw_transactionadditionalcomments",
"afw_trusttransferstatementdetail",
"afw_trusttransferstatementheader",
"afw_uisection",
"afw_uisetup",
"afw_uisetupfield",
"afw_uisetupsection",
"afw_umbrellaprem",
"afw_underwriter",
"afw_underwriting",
"afw_usage",
"afw_user",
"afw_vehicle",
"afw_vendor",
"afw_vendorinvoice",
"afw_voidedcheck",
"afw_watercraft",
"afw_workcompcert",
"afw_xdate"
2. Limit size – The size is the page size of each page to retrieve. This is set in the Paging section of the HTTP Service Source step.Ensure that the Paging checkbox is selected to see the paging options. In this example, the page size is set to 1000.
3. The field to retrieve is set after the = in the URL. In this example, it has been set at * to retrieve all the data. This can be changed to any desired field, e.g. “City”, “Name” etc depending on the data available.
4. Field Path – This is set in the Check Field tab in the Paging section of the HTTP Service Source step. In this example, it has been set to starting_Token.
HTTP Method
Set the HTTP Method for the request. This should always be set to the GET Method because this step can only extract data from the AMS360 Data Lake API.
Authentication Method
Set how to authenticate the web request. This has been set to reference the token that was generated in Flow 1.
Extracting the Data
To extract data from the AMS360 Data Lake API, follow these steps:
- Click the Execute Flow button shown in the image below:
- Click the Create Run button.
The page below shows the data that has been extracted and saved in the Buffer Destination Step. The set page limit was 1000, hence there 1000 records in the first page.
