Dear all,

This topic will begin to feel very long very fast and I am terribly sorry for that, but the main idea is actually very simple. I want to hear thoughts and suggestions on what are the viable methods for viewing external data in Salesforce.

Let us start from the beginning:
I am currently trying to implement two solutions (in order of importance):

• Getting data from an external DigData solution already up and running. It uses a NoSql Impala (by Cloudera) Database and Kafka. After the data is recieved, the final goal is to just display tables with information to our customers in the Customer Suppoort Portal
• Getting data from a (currently working only on the local system - not exposed) MySql Database on a Solaris platform into Salesforce Objects regularily and displaying the information on a visualforce page

Let us concentrate on the first topic:
A colleague who is working on this external BigData solution has offered to me three choices for connecting to this system:

• Connect directly to the Impala DB from Salesforce
• Connect to Kafka and get the data that way via an exposed gateway
• Web services (but will have to be created)

Ways to connect that I have started digging into initially:

1. Exposed web service (SOAP, REST) - we already have another working project using web services to get data from an external system, but in this case it would require a lot of work on both sides: creating the java webservices on the Impala BD endpoint and scheduled web service calls in Salesforce in order to pull data. There is also the concern of security and reliability of data transfer in this case.
2. Using "AppForce API"? - develop an external application which sends data into SF tables. This could be complicated... I do not see myself developing Dataloader 2.0 in just 1 week. It is not going to happen.
3. Using a paid solution for data integration such as:
   Informatica Cloud 
   Cast Iron 
   SnapLogic 
   Boomi 
   JitterBit 
   Sesame Relational Junction 
   Apparently these are called ETL tools. More information:
   http://salesforce.stackexchange.com/questions/71556/sql-database-to-salesforce-data-migration
4. CopyStorm -> works only one way though and it is not schedulable
5. Possible integration between external information via Kafka and Heroku and then Heroku -> Salesforce
   https://developer.salesforce.com/blogs/developer-relations/2016/09/introducing-apache-kafka-heroku-event-driven-architecture-cloud-era.html
   Heroku can use Heroku connect technology to sync with salesforce, but will have tables in lowercase on a Postgres database. Info:
   https://devcenter.heroku.com/articles/heroku-connect#how-heroku-connect-works
   I see this system working like this:
   Salesforce <-- Heroku <-- Heroku Connect <-- Postgres mirror DB <-- Kafka <-- Impalla DB???
   I am very unfamiliar with Heroku and Heroku connect. Is there any way to obtain a quick start guide and see actual code running in minutes rather than a long business talk? From what I understand, so called "Heroku apps" can be built and easily integrared with Salesforce. How easy it is to make one of these apps?
6. DataLoader + cliq
   I am currently testing this method on the second topic with the MySql DB and I am having some success with it on first glance. On windows a *.bat file can be used for scheduling an already prepared action and for UNIX or Solaris systems, a *.sh file can be used.
7. MessageBroker?
   https://www.message-broker.com/dl/mb-connector-salesforce
   I was not able to install the program due to an error (missing folder)
8. Streaming Channels
   https://developer.salesforce.com/docs/atlas.en-us.api_streaming.meta/api_streaming/create_a_streaming_channel.htm
9. Dataloader.io - tried it. Does not support DB loading or scheduling and could not map any field I want. Useless.
10. Apex Connector Framework
11. Lightning connect? oData 2.0, oData 4.0? External Salesforce Objects? (What are these. How do they work?)

After writing this down and searching through tons of articles, opening a ticket to Salesforce and being redirected here, I went crazy I have come to the following conclusions:

• Web services would work, but I will do anything that is in my power to discover at least one alternative
• AppForce api? ... I have no idea where to start. And I do not want to develop something as big as an eclipse plugin... and how will that help me anyway? I want 5 minute or 1 minute syncs. Not on demand loading
• All these ETL solutions have two things in common... nobody knows or says how one is built and second: none are free. How do I make a small ETL solution for connecting and importing data from a MySql database? anyone? Exactly.
• I was able to create a demo Postgres DB on Heroku mapped to my sandbox. Great. I added the Kafka plugin and the surprise hit me: standard0 - 1500$. How about no? Same with MySql DB.
• Dataloader + cliq might work... I could load from info from a *.cmd file, but I would prefer not to push data withut any control from a BigData solution using cliq which is lacking support since 2012.
• Streaming channels and Streaming API could be one solution. I have found an article on this:
  https://developer.salesforce.com/docs/atlas.en-us.api_streaming.meta/api_streaming/create_java_client.htm
  But what does this code do? Will it even work? Help?
• Apex Connector Framework. Apparently Salesforce can connect via the oData adapter (which is something I am having difficulty understanding what it is and how to set up) and also, a "custom Adapter for Salesforce Connect" can be created:
  https://help.salesforce.com/apex/HTViewHelpDoc?id=apex_adapter_about.htm&language=en_US
  My questions: How? What? The demos I have seen only show how to hardcode values into fictional tables. How do I tell it to connect to an actual DB type like in Java for example: ConnectionString="xyz/abc:!@#"; Database.connect(connectionString);?
  Also this example looks scary. Where do I even start?
  https://www.youtube.com/watch?v=_03T6U7jWX0
• Apex Bulk API is something talked about often and MetadataApi as well... but I do not thing this can be programed to do what I want... can they?
• The most important aspect: how do I tell the external app/system what my credentials are? and retian them forever? I do not want to save plain text admin credentials on another machine. I could use OAUTH2.0 and login, but won't I need a session Id every 12 hours? How do I solve this?

Which leads me to (what I believe to be) my only options:

• Webservices
• Appforce api
• Dataloader + cliq
• Streaming Channels
• Apex connection framework
• Extract, Transform, Load (ETL) solutions

And I am stuck. Please explain to me how I could start implementing any of these solutions and if these are in any way viable. I would like some more information about literally anything even remotely related to these.

Also in the second topic where I have tried import data from a MySql I am still testing the validity of the DataLoader + cliq method.

What are your thoughts on this?



Tons of links:
Ways to connect:
https://help.salesforce.com/HTViewSolution?id=000109985

Talend
https://www.talend.com/resources/podcast-videocast/integrating-with-salesforce

Connect Kafka to Heroku
https://developer.salesforce.com/blogs/developer-relations/2016/09/introducing-apache-kafka-heroku-event-driven-architecture-cloud-era.html

Ticket Salesforce
CopyStorm

Heroku connect
https://devcenter.heroku.com/articles/heroku-connect#how-heroku-connect-works
https://www.youtube.com/watch?v=PV7RsqF32Io

Salesforce APIs:
https://developer.salesforce.com/page/Salesforce_APIs
https://help.salesforce.com/HTViewHelpDoc?id=integrate_what_is_api.htm&language=en_US

Streaming Salesforce Events to Heroku Kafka:
https://developer.salesforce.com/blogs/developer-relations/2016/05/streaming-salesforce-events-heroku-kafka.html
https://developer.salesforce.com/docs/atlas.en-us.api_streaming.meta/api_streaming/create_a_streaming_channel.htm
https://www.youtube.com/watch?v=Aq_TnjU0q48

Discussions:
https://developer.salesforce.com/forums/?id=906F00000008rFZIAY
https://developer.salesforce.com/forums/?id=906F00000008rUbIAI
https://developer.salesforce.com/forums/?id=906F0000000AtYAIA0

Expanding Visibility With Apache Kafka
https://medium.com/salesforce-engineering/expanding-visibility-with-apache-kafka-e305b12c4aba#.aunh2sltw
OK EVENT LOG
https://medium.com/salesforce-engineering/the-architecture-files-ep-3-ok-event-log-56ab8060c546#.ob0h2p7sd

Real-time access to Salesforce data
https://www.progress.com/connectors/salesforce
http://www.dreamhouseapp.io/installation/
https://github.com/jcustenborder/kafka-connect-salesforce

Other:
Integrating Force.com with Your On-premise Database:
https://developer.salesforce.com/page/Integrate_Salesforce.com_With_Your_On-premise_Database
http://www.nonprofitsoapbox.com/solutions/salesforce-integration
http://support.picnet.net/entries/27733608-Soapbox-Forms-for-Salesforce-Current-1-3-
https://www.youtube.com/watch?v=rGYvL8jhmCY
https://www.youtube.com/watch?v=W7Tfru19nS0

http://www.slideshare.net/developerforce/df13-using-external-objects-for-integration-mt
http://www.odata.org/


Best Regards,
Alex.