Test source connection outside Infoworks product

Test source connection outside Infoworks product

The aim of this article is document the steps to test the connection outside the Infoworks product. This is mainly for internal debugging purposes and is not intended to be a customer facing article.

SQL Server source:

1.Download below files.

<https://aflac-jar-drop.s3.amazonaws.com/sql-server-test/SQLDatabaseConnection.java>

<https://aflac-jar-drop.s3.amazonaws.com/sql-server-test/mssql-jdbc-7.4.1.jre8.jar>

2. Run the below commands to test the connection

javac SQLDatabaseConnection.java

java -cp .:mssql-jdbc-7.4.1.jre8.jar SQLDatabaseConnection

Note: Currently, the user and password files in the file wrong. One can edit file and pass the correct values and retry following below instructions

rm SQLDatabaseConnection.class

javac SQLDatabaseConnection.java

java -cp .:mssql-jdbc-7.4.1.jre8.jar SQLDatabaseConnection

Kafka and Confluent Kafka source:

1. Assuming user is root on master node. Create a kafka directory. mkdir kafka

2. Download the dependencies

wget <https://repo1.maven.org/maven2/org/apache/kafka/kafka-clients/2.5.0/kafka-clients-2.5.0.jar> wget <https://repo1.maven.org/maven2/org/slf4j/slf4j-log4j12/1.7.25/slf4j-log4j12-1.7.25.jar>

wget <https://repo1.maven.org/maven2/org/slf4j/slf4j-api/1.7.25/slf4j-api-1.7.25.jar>

3. Run below command which test the connection (i.e. list the topics)

javac -cp ".:/root/kafka/*" consumer1.java java -cp ".:/root/kafka/*" consumer1

Ingest the data from cdata source outside Infoworks

Example: GoogleCM

1. Launch the spark-shell and the below commands:

val query = """(select Ad,AdId, BrowserPlatform,PlatformType,Creative, CreativeId,AdStatus, AdType, Advertiser,AdvertiserId,Campaign, CampaignId,PackageRoadblock, PackageRoadblockId,ActivityClickThroughConversions, ActivityClickThroughRevenue,Clicks,ClickThroughUrl,ClickRate,ActivityId,Activity, ActivityGroup, ActivityGroupId,impressions, Site, SiteDirectory, SiteDirectoryId, SiteId, SiteKeyname,ActivityViewThroughConversions, ActivityViewThroughRevenue,MediaCost,Placement, PlacementId,ActiveViewEligibleImpressions, ActiveViewMeasurableImpressions, ActiveViewViewableImpressions from StandardReport where DateRange = 'TODAY')"""

val sparksql_df = spark.sqlContext.read.format("jdbc").option("url", "jdbc:googlecm:AuthScheme=\"OAuthJWT\";ProfileID=\"7096381\";InitiateOAuth=\"GETANDREFRESH\";OAuthClientId=\"\";OAuthJWTCert=\"/home/revanth_reddy/credentials.json\";OAuthJWTCertType=\"GOOGLEJSON\";Logfile=\"/tmp/gcm_cdata_jar.log\";Verbosity=\"5\";RTK=45505247565A30313231323233305745425452314131000000000000000000000000000000000000444B375A37524E5300003737504447555956365954330000;OAuthClientId=721247915958-28h762v7uml3h1q75mk9t4g97ggnn78g.apps.googleusercontent.com;OAuthClientSecret=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").option("dbtable",query).option("driver","cdata.jdbc.googlecm.GoogleCMDriver").load()

Note: All the quotes for values of the connection strings options need to be escaped. Another RTK if the above one does not work

RTK=2020,DVRFV,445652465641535552425641454E5450334D46453233313900000000000000000000000000000000494E464F574F524B00004E5A533543385746424547390000;

Write to target directory and creating a table.

sparksql_df.write.format("parquet").option("path", "gs://support_test/spark_cdata_custom_query_gcm_2").saveAsTable("default.standardreport")