https://www.kinetica.com/assets/connect-to-100s-of-data-sources-wi…t-a-few-clicks-or-lines-of-code-Ds80YJZT.js

1const e="connect-to-100s-of-data-sources-with-just-a-few-clicks-or-lines-of-code",a="Connect to 100s of data sources with just a few clicks",t="A modern business relies on a variety of repositories for data. These include databases like Postgres, object stores like AWS S3, event stores like Apache Kafka, file storage solutions like Google Drive and applications like Salesforce and HubSpot. All of these databases and applications serve specific business needs. For instance, an online retail business might use Apache Kafka to record customer interaction with their website, Cassandra for long term data storage, Hubspot for managing business relationships and dropbox for managing internal files.  Analytical databases like Kinetica need to be able to access and analyze data from all of these different sources in an easy and performant manner.  But this is easier said than done. Each of these repositories have their own often unique take on how to represent, store and provide access to data. This blog explains how Kinetica uses JDBC (Java DataBase Connectivity) and drivers provided by the data connectivity platform CData  to address this challenge. There’s also a sample workbook at the end of this blog […]",s="2022-09-09",o="Hari Subhash",n="https://kinetica-web-assets.s3.us-east-1.amazonaws.com/assets/blog/image1-1024x576.png",r=["Kinetica In Motion"],i=`<p class="text-gray-600 leading-relaxed mb-4">A modern business relies on a variety of repositories for data. These include databases like Postgres, object stores like AWS S3, event stores like Apache Kafka, file storage solutions like Google Drive and applications like Salesforce and HubSpot.</p>
2
3<img src="https://kinetica-web-assets.s3.us-east-1.amazonaws.com/assets/blog/image2-1024x576.png" alt="" loading="lazy" class="rounded-lg shadow-md my-6 max-w-full h-auto">
4
5<p class="text-gray-600 leading-relaxed mb-4">All of these databases and applications serve specific business needs. For instance, an online retail business might use Apache Kafka to record customer interaction with their website, Cassandra for long term data storage, Hubspot for managing business relationships and dropbox for managing internal files.&nbsp;</p>
6
7<p class="text-gray-600 leading-relaxed mb-4">Analytical databases like Kinetica need to be able to access and analyze data from all of these different sources in an easy and performant manner.&nbsp;</p>
8
9<p class="text-gray-600 leading-relaxed mb-4">But this is easier said than done. Each of these repositories have their own often unique take on how to represent, store and provide access to data. This blog explains how Kinetica uses JDBC (Java DataBase Connectivity) and drivers provided by the data connectivity platform CData&nbsp; to address this challenge.</p>
10
11<p class="text-gray-600 leading-relaxed mb-4">There's also a sample workbook at the end of this blog that uses JDBC to connect and load data from a google spreadsheet and a Postgres database. You can try that for free with <a href="https://cloud.kinetica.com/trynow/" class="text-kinetica-600 hover:underline">Kinetica Cloud</a></p>
12
13<h2 class="text-2xl font-bold text-gray-900 mt-8 mb-4" id="h-an-emphasis-on-ease-of-use">An emphasis on ease of use</h2>
14
15<p class="text-gray-600 leading-relaxed mb-4">Kinetica is known for its speed. Its high performance vectorized engine and custom built library of <a href="/features/geospatial-analytics" class="text-kinetica-600 hover:underline">geospatial</a>, <a href="/features/graph-analytics" class="text-kinetica-600 hover:underline">graph</a>, <a href="/features/architecture" class="text-kinetica-600 hover:underline">OLAP</a> and <a href="/features/real-time-analytics" class="text-kinetica-600 hover:underline">time series functions</a> allow you to do complex analytical tasks on extremely large data.&nbsp;</p>
16
17<p class="text-gray-600 leading-relaxed mb-4">Over the last year, we have added several ease of use features that now couple our industry-beating performance with a frictionless user experience. These include a rich and interactive SQL notebook environment called Workbench, a Postgres wireline that allows users to simply point existing applications that use Postgres syntax to Kinetica without having to refactor code and JDBC data sources that allow you to connect to hundreds of different data sources with ease.</p>
18
19<p class="text-gray-600 leading-relaxed mb-4">The last feature discussed above – JDBC data sources – is key to Kinetica's ability to plug into a variety of data repositories. Let's take a closer look.</p>
20
21<h2 class="text-2xl font-bold text-gray-900 mt-8 mb-4" id="h-two-paths-custom-connectors-or-jdbc">Two paths – Custom connectors or JDBC</h2>
22
23<p class="text-gray-600 leading-relaxed mb-4">Generally speaking, there are two ways to establish a connection with a data source – you could either build a custom connection from scratch, or you could rely on an existing protocol.&nbsp;</p>
24
25<p class="text-gray-600 leading-relaxed mb-4">Custom connectors offer the greatest amount of flexibility and control over performance, since you can tweak and tune it so that it best suits your needs. Custom connectors are however, difficult and time consuming to build and maintain.&nbsp;</p>
26
27<p class="text-gray-600 leading-relaxed mb-4">Generalized protocols like JDBC on the other hand, provide an out of the box experience when it comes to connecting to a data source. But they offer, lesser degree of flexibility and control since you have to rely on the generalized interface provided by the JDBC driver rather than one that you have tuned to work best with your solution.</p>
28
29<p class="text-gray-600 leading-relaxed mb-4">At Kinetica, we have opted for a hybrid approach.&nbsp;</p>
30
31<p class="text-gray-600 leading-relaxed mb-4">We provide custom interfaces for all the data sources that we are tightly coupled with. These include batch data stores like HDFS, AWS S3, Azure blob store and Google Cloud Platform and the most popular solution for streaming data, Apache Kafka. For everything else we provide an interface via JDBC.</p>
32
33<img src="https://kinetica-web-assets.s3.us-east-1.amazonaws.com/assets/blog/image1-1024x576.png" alt="" loading="lazy" class="rounded-lg shadow-md my-6 max-w-full h-auto">
34
35<h2 class="text-2xl font-bold text-gray-900 mt-8 mb-4" id="h-what-is-jdbc">What is JDBC?</h2>
36
37<p class="text-gray-600 leading-relaxed mb-4">JDBC stands for Java DataBase Connectivity. It is a standardized API for interacting with databases using Java programs. With JDBC developers don't have to worry about building custom connectors for interacting with a new database. Instead, you can use JDBC as a middle layer that provides a standardized interface to connect, issue queries and handle results from a database.&nbsp;</p>
38
39<p class="text-gray-600 leading-relaxed mb-4">The only requirement is that the application or database that you are connecting to has a JDBC driver. And this is where Kinetica's partnership with CData comes into play.</p>
40
41<h2 class="text-2xl font-bold text-gray-900 mt-8 mb-4" id="h-access-100s-of-data-sources-using-cdata">Access 100s of data sources using CData</h2>
42
43<p class="text-gray-600 leading-relaxed mb-4"><a href="https://www.cdata.com/" target="_blank" rel="noreferrer noopener" class="text-kinetica-600 hover:underline">CData</a> is a data connectivity platform that provides JDBC drivers for hundreds of databases and applications.</p>
44
45<p class="text-gray-600 leading-relaxed mb-4">These include NoSQL databases like MongoDB, Redis and Cassandra, relational databases like Postgres, MySQL and Oracle, File stores like Dropbox and Google Drive and business tools like Salesforce, Google Analytics and NetSuite.</p>
46
47<p class="text-gray-600 leading-relaxed mb-4">CData does all the work of creating and maintaining all the JDBC drivers that provide access to data from all of these data sources. And as a user of Kinetica, you get access to all of these drivers for free.</p>
48
49<p class="text-gray-600 leading-relaxed mb-4">Now, let's see how you can use a JDBC driver to connect to a data source.</p>
50
51<h2 class="text-2xl font-bold text-gray-900 mt-8 mb-4" id="h-connect-with-just-two-queries">Connect with just two queries</h2>
52
53<p class="text-gray-600 leading-relaxed mb-4">Now, there are two ways in which you can use a JDBC driver to connect to a data source. You can load your own driver into Kinetica or you can reference a CData driver. The example workbook shared at the end of this blog uses both routes. We use the CData driver to access data from a google spreadsheet and then we load a publicly available driver into Kinetica to connect to a Postgres database.</p>
54
55<p class="text-gray-600 leading-relaxed mb-4">No matter the path, the steps for loading data are easy and intuitive. The code below shows how to connect to a postgres database using a JDBC driver for postgres.&nbsp;</p>
56
57<p class="text-gray-600 leading-relaxed mb-4">First you create the data source. This requires the location of the database along with the credentials for accessing it. In the options, we specify the location of the JDBC driver and the driver class.</p>
58
59<pre class="bg-gray-900 text-gray-100 rounded-lg p-4 overflow-x-auto mb-4 text-sm font-mono"><code>CREATE OR REPLACE DATA SOURCE postgres_ds
60LOCATION = 'jdbc:postgresql://mydb.com:5432/db'
61USER = 'myusername'
62PASSWORD = 'mypassword'
63WITH OPTIONS (
64  JDBC_DRIVER_JAR_PATH = 'kifs://drivers/postgresql-42.3.6.jar',
65  JDBC_DRIVER_CLASS_NAME = 'org.postgresql.Driver'
66);</code></pre>
67
68<p class="text-gray-600 leading-relaxed mb-4">And then you specify the table in Kinetica and the name of the file or the query that selects the data that you want to load into it from the data source created in the previous step.</p>
69
70<pre class="bg-gray-900 text-gray-100 rounded-lg p-4 overflow-x-auto mb-4 text-sm font-mono"><code>LOAD DATA INTO my_kinetica_table
71FROM REMOTE QUERY 'SELECT * FROM public.large_table'
72WITH OPTIONS (
73   DATA SOURCE = 'postgres_ds'
74);</code></pre>
75
76<p class="text-gray-600 leading-relaxed mb-4">

76That's it – just two simple SQL queries. We can use the same pattern as shown above to connect to hundreds of different data repositories using either custom connectors or JDBC. All you need are the connection details and the relevant credentials.&nbsp;</p>
77
78<h2 class="text-2xl font-bold text-gray-900 mt-8 mb-4" id="h-using-the-interface">Using the interface</h2>
79
80<p class="text-gray-600 leading-relaxed mb-4">Kinetica's workbench also comes with a point and click interface for connecting to native and JDBC data sources. This is an easy code free way to create a data source and start loading data from it into Kinetica.</p>
81
82<img src="https://kinetica-web-assets.s3.us-east-1.amazonaws.com/assets/blog/image3-1014x1024.png" alt="" loading="lazy" class="rounded-lg shadow-md my-6 max-w-full h-auto">
83
84<h2 class="text-2xl font-bold text-gray-900 mt-8 mb-4" id="h-try-it-yourself">Try it Yourself</h2>
85
86<p class="text-gray-600 leading-relaxed mb-4">The following <a href="https://github.com/kineticadb/examples/tree/master/jdbc_data_sources" target="_blank" rel="noreferrer noopener" class="text-kinetica-600 hover:underline">repo</a> contains a workbook that you can load into Kinetica to try this out on your own. You can run <a href="https://cloud.kinetica.com/trynow/" class="text-kinetica-600 hover:underline">Kinetica in the cloud</a> for free.</p>
87
88<h2 class="text-2xl font-bold text-gray-900 mt-8 mb-4" id="h-resources">Resources</h2>
89
90<p class="text-gray-600 leading-relaxed mb-4">You can find more information about loading data into Kinetica from our <a href="https://docs.kinetica.com/7.1/load_data/" target="_blank" rel="noreferrer noopener" class="text-kinetica-600 hover:underline">documentation website</a>.</p>
91
92<h2 class="text-2xl font-bold text-gray-900 mt-8 mb-4" id="h-contact-us">Contact us</h2>
93
94<p class="text-gray-600 leading-relaxed mb-4">We're a global team, and you can reach us on <a href="https://join.slack.com/t/kinetica-community/shared_invite/zt-1bt9x3mvr-uMKrXlSDXfy3oU~sKi84qg" target="_blank" rel="noreferrer noopener" class="text-kinetica-600 hover:underline">Slack</a> with your questions and we will get back to you immediately.</p>`,l={slug:e,title:a,excerpt:t,date:s,author:o,featuredImage:n,categories:r,body:i};export{o as author,i as body,r as categories,s as date,l as default,t as excerpt,n as featuredImage,e as slug,a as title};