2
$\begingroup$

In order to build a classifier, I need to extract a few features from the data stored on a MySQL database. I need to join multiple tables and it is taking a lot of time. I have joined 2 tables at one time and have got results in multiple cases. I need to combine them. Writing a script will be the best option? How do people extract features from large relational databases? Am I missing something?

Thanks.

Improve this question
$\endgroup$
6
  • 1
    $\begingroup$ Is the problem that the join is taking a long time? Do you mind explaining your features? In general I advise doing the heavy lifting in the database. $\endgroup$
    – Emre
    Apr 13, 2016 at 6:10
  • $\begingroup$ Mine is a customer database, having billing details of customer in one table, usage details in another one, customer personal details in a third table, interactions with customer care in a fourth table etc. The final feature file is more or less an aggregated file. It is painful! $\endgroup$
    – pnv
    Apr 13, 2016 at 7:18
  • $\begingroup$ Can you give an example data of all those databases, and the amount of rows you are joining? $\endgroup$
    – João Almeida
    Apr 13, 2016 at 10:08
  • $\begingroup$ It's around 4 tables, with 1 lakh + records, and an average of 10 rows per table..I don't think it is still huge. $\endgroup$
    – pnv
    Apr 13, 2016 at 11:15
  • $\begingroup$ 10 rows and 4 tables , not that heavy! You might have large number of features. This is what matters. $\endgroup$
    – user119783
    Jul 16, 2021 at 8:04

1 Answer 1

Reset to default
0
$\begingroup$

There are two end of the spectrum:

  1. Write a SQL query that creates a single materialized views that formatted in such a way that it is ready for machine learning.

  2. Write several SQL queries that fetch all possibly relevant data from the database. Then munge them in another system to create a table ready for machine learning.

It is often times the combination of those of two strategies.

The exact strategy depends on SQL query writing ability, size of the data, size of machine executing the database query, and ability to munge the data outside of SQL.

From personal experience, prioritize whatever works to get the job done or optimizing the workflow.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.