JobName as .sql file in config file

[diegomsg]

It would be nice to have the ability to address a .sql query file as JobName in config file.

[pacman82]

Hello @diegomsg ,

thanks, for the suggestion. What is your usecase? What is preventing you from sourcing a query from a file using the tools of your shel?. E.g. bash:

odbc2parquet --dsn test_db out.par "$(cat query.sql)"

Best regards,
Markus

[diegomsg]

Great. Didn't try this before.
Didn't even know that I could call file content inside function with "$(cat query.sql)"

Had just a few problems withe commented .sql files using -- characters. Searching how to fix without reviewing all queries. Some help with that?

The queries are used in sqlcmd batch scripts.

[pacman82]

How about parsing them and emitting them without the comments?

import sqlparse

sql_example = """--comment
SELECT * from test;
INSERT INTO test VALUES ('
-- test
a
');
 """
print sqlparse.format(sql_example, strip_comments=True).strip()

Taken from this Stack Overflow Question: https://stackoverflow.com/questions/5871791/howto-clean-comments-from-raw-sql-file

Best, Markus

[diegomsg]

Thanks.

I was able to query to parquet using your tool in windows terminal.
odbc2parquet query --dsn $odbc_connected_server output_file.par "$(Get-Content ($query_folder + '\' + $query) -Encoding utf8)"

The comments inside sql queries were striped using R, rewriting multiple files

library(stringr)
library(purrr)

# Define function
remove_comments <- function(file_path) {
 # Read files
  sql_text <- readLines(file_path, encoding = "latin1")

  # Remove comments
  sql_text <- str_remove_all(sql_text, "--.*$")
  sql_text <- str_remove_all(sql_text, "/\\*.*?\\*/")

  # Uncommented files
  return(sql_text)
}

# Define folder
folder_path <- "complete\\folder\\path"

# List sql files in folder
sql_files <- list.files(folder_path, pattern = "\\.sql$", full.names = TRUE)

# Remove comments
sql_texts <- map(sql_files, remove_comments)

# Write to files
walk2(sql_files, sql_texts, ~writeLines(.y, .x))