1C: ERP back end DB tables to the data lake/data warehouse

what are the best practices when it comes to extracting data from the 1C: ERP back end DB tables to the data lake/data warehouse both for historical pull as well as for daily/real time data ingestion

Hello Vaisakh,

a very interesting question!

In general, due to the fact that 1C assigns SQL tables names like "_Document292" or "_AccumRg7110", it is quite difficult to get the data we need directly with SQL queries.

Therefore, one of the options for transferring data to the Storage may be as follows:

for example, at the end of the day, a procedure is started in 1C, which first selects the necessary data using 1C methods, and then this data is written to the right place.

To do this, we used to use direct SQL queries, now the "External Data Source" object is available in 1C, which allows us to directly connect to various databases, including SQL.

Thanks for the quick response !!!


when we consider "External Data Source" , do we have the option to store the data to destination as cloud storage . For example , if we consider azure as the destination can we store the data from 1C platform to azure blob storage ?Also if we have to extract history load as well as incremental load(daily load) can we have the option to schedule it? Also how we can extract only changed data from 1C platform?

Hello Vaisakh,

first, about "External Data Sources": these objects work with ODBC drivers installed in the system. Therefore, Azure is not suitable as a data destination.

When integrating 1C and Azure, they usually make an intermediate database. First, the data is uploaded from 1C to this intermediate database, while being converted into a convenient representation (for example, tables are given understandable names) and only then are uploaded from the intermediate database to Azure.

Instead of an intermediate database, Views can also be used - this will avoid uploading data from 1C to another database.

Now about how to upload from 1C only the changed data. I'm currently preparing an article on this topic.

Please follow our updates here: https://1c-dn.com/blog/

The article should appear next week.