How to Set PRAGMA foreign_keys = ON in SQLite via DataGrip

I think the answer mainly depends on how much time do you want to spend learning new databases vs how much time to you want to spend learning machine learning.

For example PostgreSQL has lots of GIS stuff built-in which I assume could be very useful for your queries. CouchDB has many useful features with their map/reduce stuff but I find it a bit limited. If you think you're going to add many columns later on based on new algorithms you might want to look into the column-oriented databases like Apache Cassandra.

However, I would recommend splitting the problem into two parts:

• Data gathering
• Data analysis

The first part you seem to have a good plan for already. I would write a super simple app that would just suck in the data from twitter and put it in a table as a BLOB. Or just a file on disk.

When you have that you can go from the raw data and insert it into any kind of backend. Now you can choose backend based on the current problem (algorithm) you're working on, instead of having one solution to fit all your problems. The key point here is to extract exactly the data you need. That way you shouldn't have to consider the fact that the data is nested inside a document from Twitter as you only pick out the parts you need. If you go that way I think an RDBMS would perform very nice as they can run all kinds of queries.

Make sense?

The problem you have is caused by sqlite not enforcing its own field definitions. This means a field defined as varchar(50) might have more than 50 characters in it.

Run some select statements over your data to check field lengths. For example:

Select field1, length(field1)
Where length(field1) > 50

One possible way around it is to use lazy schema validation. Look at your linked server properties.

Lazy schema validaton is an enterprise only feature. If you dont have enterprise you could install developer on your PC to do the extraction.

Another option might be to edit your target table to have bigger fields and then use an openquery statement and substring each field to be less than target field length. Maybe even use varchar(max).

When i had this same problem, i got around it using excel, as excel isnt as fussy with field lengths. But im not sure how it would handle images, or the volume of data.

As i now have sql 2016 developer on my pc i would using that.