Windows cmd shell script for dynamically generating JDBC.Properties file (A.C. 1.4)
Use Case:
Batch import automation scripts using a SQL query in Anaplan Connect (AC) 1.4 require a companion .properties file that contains a lot of information about the query (connect string, login credentials, SQL statement, parameters). As a best practice, you should avoid hardcoding usernames and passwords in plaintext or having redundant information that may change in multiple .properties files. A Windows shell script named createJDBC.properties is provided that dynamically creates a temporary jdbc.properties file on the fly from secure, centralized information.
Disclaimer:
Any computer code in this post is provided "as-is", there is no guarantee of fitness for purpose. As the implementer, you are responsible for understanding it, modifying it to your situation, testing it, and taking precautions to avoid any damages that may result from use.
Background:
Anaplan Connect 1.4 uses a .properties file when connecting to most relational databases such as Oracle, MySQL, and many more. (AC connections to MS SQL Server are the exception, those do not use a .properties file as of the time this was written.) The contents of the .properties file need to look something like this:
jdbc.connect.url= jdbc:oracle:thin:server:server.domain.com:1521:db_edw
jdbc.username=sysaccount
jdbc.password=sTr0ngP4ssW0rdHerE
jdbc.fetch.size=10
jdbc.isStoredProcedure=false
jdbc.query= Select Descr || ', ' || State || ': ' StoreNum FROM Stores WHERE Open_Date>(Select sysdate from Dual)
As you can see, there are Oracle login credentials and a SQL statement that may require periodic maintenance. A batch process for updating your data hub might involve a dozen queries, each stored in a separate .properties file. Do you really want to hardcode this information in a dozen files? It would be better to generate the .properties file on-the-fly as your process requires it and to delete it immediately after use.
Another nuisance issue: For SQL queries to be maintainable they must be readable by people and readability requires line breaks. Which code would you rather maintain:
SELECT A.Descr || ', ' || A.State || ': ' A.StoreNum as Name, B.Region as Parent, A.StoreNum as Code FROM Stores A inner join StoreStatus B on A.StoreNum=B.StoreNum WHERE B.Open_Date<(Select sysdate from Dual)
or
SELECT
A.Descr || ', ' || A.State || ': ' A.StoreNum as Name,
B.Region as Parent,
A.StoreNum as Code
FROM
Stores A inner join Store_Info B on A.StoreNum=B.StoreNum
WHERE
B.Open_Date<(Select sysdate from Dual)
Both are the same query, but the second one is much easier to understand and update.
You may be asking "Why not use Views in the relational database?" I agree, and I will generally advise anyone to store complicated SQL statements in their relational database as a view. This allows you to have VERY simple query logic in your AC script: SELECT * FROM <name of view>. However, on a recent project the client felt it would be too cumbersome to work with their Oracle DBA to change the view definition whenever they needed minor changes to the query logic. Anaplan models can be quite flexible, they felt they needed the same flexibility to modify the data source for their import processes. For them, it would be better to keep control of their complex SQL queries in the business.
A Dynamic Alternative:
Here's a dynamic approach that solves several problems:
- Maintain connection and login information in a centralized encrypted master file (Globals.bat). This .bat sets temporary variables that are then used in later steps of the process.
The contents of Globals.bat look like this:
SET jdbcConnection=<connect string to database>
SET jdbcUsername=<database username>
SET jdbcPassword=<database password>
SET anaplanUser=<Anaplan username:password>
For security, it is recommended that this file be encrypted. Make sure the userid that will run your scheduled batch tasks has the ability / privileges needed to read the encrypted file. - Maintain SQL scripts in another centralized master file (SQLQueries.txt). The contents of SQLQueries.txt should look like this:
::<name of query 1>
<SQL statement 1>
::end
::<name of query 2>
<SQL statement 2>
::end
So for our example SQL Statement above, SQLQueries.txt would include a section like the following.
::StoreHierarchy
SELECT
A.Descr || ', ' || A.State || ': ' A.StoreNum as Name,
B.Region as Parent,
A.StoreNum as Code
FROM
Stores A inner join Store_Info B on A.StoreNum=B.StoreNum
WHERE
B.Open_Date<(Select sysdate from Dual)
::end - CreateJDBCProperties.bat: copy-paste this content to create this utility script. The only modifications you'll need are the three lines that set the variables jdbcPropertiesFilePath, sqlQueriesFilePath, and LogFilePath.
cls
rem SETLOCAL EnableDelayedExpansion
@echo offREM Set location of jdbc.properties file, sqlQueries.txt, and log file
set jdbcPropertiesFilePath=c:\Anaplan\anaplan-connect-1.4\jdbc.properties
set sqlQueriesFilePath=c:\Anaplan\anaplan-connect-1.4\scripts\sqlQueries.txt
set LogFilePath=c:\Anaplan\anaplan-connect-1.4\ErrorLogs\jdbcproperties.logREM Test for missing required arguments, (1-8 are required.)
REM If any are missing, show help message
if "%~1"=="" set msg=Missing required argument(s^)
if "%~2"=="" set msg=Missing required argument(s^)
if "%~3"=="" set msg=Missing required argument(s^)
if "%~4"=="" set msg=Missing required argument(s^)
if "%msg%"=="" goto resume
echo ************* %msg% **************
echo USAGE: CreateJDBCProperties [dbuser] [dbpwd] [jdbcconnection] [sqlqueryname] [fetchsizemb] [sqlparams]
echo.
echo dbuser: Userid for relational database [required]
echo dbpwd: Password for relational database [required]
echo jdbcconnection: Connect string to database [required]
echo sqlqueryname: Name identifying sql statement in sqlqueries.txt file. [required]
echo dbfetchsize: databasefetchsize number of rows integer [optional]
echo sqlargs: If sql statement uses tokens (?), values to be inserted [optional]
echo.
echo EXAMPLE:
echo CreateJDBCProperties sysaccount syspwd123$ jdbc:oracle:thin:server.domain.com:1521:edw ^"Sales by Division and Period^" 8 ^"2019,Jan,'Div 12'^"
echo.
echo SQL Query must be found in sqlqueries.txt file
echo Query length limited to ~8100 characters
echo Query must be preceeded by ::<name of query> and must followed by ::end. Case sensitive.
echo.
echo Parameters for use in ? tokens in your SQL statement are optional. On the command line, enclose
echo the complete parameter list in double quotes, put each parameter value with spaces in single quotes ''.
echo.
echo Example SQL query:
echo ::Sales by Division and Period
echo Select Product_ID, Customer_ID, Div_ID, Year, Period, Units, Amount
echo From Tbl_Transactions
echo where Year=? and
echo Period in (?) and
echo Div_ID in (?)
echo ::end
goto end
:resumecall :tee START
REM Assign command line arguments 1-8 to variables.
set dbuser=%~1
set dbpwd=%~2
set jdbcConnection=%~3
set sqlqueryname=%~4
if "%~5"=="" (
set fetchsizemb=10
) else (
set fetchsizemb=%~5
)
REM Get query parameters from arg6. (Extra code needed to handle comma separated list of params)
ECHO %1 %2 %3 %4 %5 %6 >x&FOR %%? IN (x) DO SET /A strlength=%%~z? - 2&del x
set queryparams=%~6REM if jdbc.properties exists, delete it
if exist %jdbcPropertiesFilePath% (del %jdbcPropertiesFilePath%)
REM Create new empty jdbc.properties file
echo .> %jdbcPropertiesFilePath%REM Set User, password, fetch size
set jdbcUser=jdbc.username=%dbuser%
set jdbcPwd=jdbc.password=%dbpwd%
set jdbcFetch=jdbc.fetch.size=%fetchsizemb%
set jdbcIsStoProc=jdbc.isStoredProcedure=falseREM **** Find named multiline query in sququeries.txt ************
set queryfound=false
REM Loop through file line by line, find named query, write to sqlstring
setlocal EnableDelayedExpansion
for /f "usebackq tokens=*" %%a in ("%sqlQueriesFilePath%") do (
set myline=%%a
if !myline!==::end set queryfound=false
if !queryfound!==true set sqlstring=!sqlstring! %%a
if !myline!==::%sqlqueryname% set queryfound=true
)REM Add escape characters before < > | & and \ characters
for %%B in (^< ^> ^| ^& ^\) do (
set sqlstring=!sqlstring:%%B=^^^%%B!
)
rem set sqlstring=!sqlstring:"=""!set jdbcSQL=jdbc.query="%sqlstring%""
set jdbcQryParams=jdbc.params="%queryparams%"REM ********* Create jdbc.properties file *************
echo jdbc.connect.url= %jdbcConnection:"=%>%jdbcPropertiesFilePath%
call :tee jdbc.connect.url= %jdbcConnection:"=%
echo %jdbcUser%>>%jdbcPropertiesFilePath%
call :tee %jdbcUser%
echo %jdbcPwd%>>%jdbcPropertiesFilePath%
echo jdbcFetch is %jdbcFetch%
call :tee jdbc.pwd=************
echo %jdbcFetch%>>%jdbcPropertiesFilePath%
call :tee %jdbcFetch%
echo %jdbcIsStoProc%>>%jdbcPropertiesFilePath%
call :tee %jdbcStoProc%
echo %jdbcSQL:"=%>>%jdbcPropertiesFilePath%
call :tee %jdbcSQL%
if defined %jdbcQryParams% (
echo %jdbcQryParams%>>%jdbcPropertiesFilePath%
call :tee %jdbcQryParams%
):end
call :tee END
exit /B 0
:: a function to write to a log file and write to stdout
:tee
@echo off
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ('time /t') do (set mytime=%%a%%b)
echo %mydate%_%mytime% : %* >> %LogFilePath%
ECHO %*
EXIT /B 0 - Put it all together: each Anaplan Connect script will
a) Set some local variables,
b) Call Globals.bat
c) Call CreateJDBCProperties.bat,
d) Set the OPERATION string,
e) Pass all the necessary parts to AnaplanClient.bat.
f) Delete the jdbc.properties file
The contents of a typical Anaplan Connect script would look like this:
REM set workspace ID, model ID, and SQL query name
SET workspaceId=<Anaplan workspace ID>
SET modelId=<Anaplan model ID>
SET anaplanFileName=<name of file to replace in Anaplan>
SET anaplanActionType=<process|import|export>
SET anaplanImportAction=<name of action (process, import, export) to run in Anaplan>
SET rejectLogFolder=<path to folder for rejected records logging>
SET processLogFile=<full path to file where AC 'chatter' will be logged>
SET sqlQueryName=<name of query in SQLQueries.txt>
REM *** End of settings - Do not edit below this line ***
CALL ./Globals.bat
CALL ./CreateJDBCProperties.bat %jdbcUsername% %jdbcPassword% %jdbcConnection% %sqlQueryName%set Operation=-service "https://api.anaplan.com" -auth "https://auth.anaplan.com" -file %anaplanFileName% -chunksize 5 -jdbcproperties%jdbcPropertiesFilePath% -%anaplanActionType% %anaplanAction% -execute -output %rejectLogFolder%
setlocal enableextensions enabledelayedexpansion || exit /b 1
cd %~dp0
if not %anaplanUser% == "" set Credentials=-user %anaplanUser%
set Command=.\AnaplanClient.bat %Credentials% -workspace %workspaceId% -model %modelId% %Operation% .> %processLogFile%
@echo %Command%
cmd /c %Command%
set errorCode=%ERRORLEVEL%
del %jdbcPropertiesFilePath%
exit /b %errorCode%
Answers
-
-
Love this @hendersonmj - I love it when we geek out on Anaplan. This is my kind of post - just added it to my sacred list of important Community posts.
I would just mention that for $15-$30/year you can purchase a CA certificate and avoid the password altogether, not to mention the issue of having to reset (usually 90 days).
Love the property capture though - I have something similar I use in Python.
0
