Skip to content

Latest commit

 

History

History
420 lines (334 loc) · 15 KB

README.md

File metadata and controls

420 lines (334 loc) · 15 KB
Raw

CWTS ETL tooling

Version: 8.1.1

Description

This repository contains all reusable components for running CWTS data pipelines as well as some rudimentary error handling and function utilities for Windows CMD (.bat) scripts. Be aware that the code in this repository is highly dependent on the CWTS hardware infrastructure and applies settings that are specifically tuned to CWTS use cases.

Usage

Generally, a copy of this repository should be placed at the root of a CWTS data pipeline. The scripts in the /functions folder can then be called relative to the current .bat script: call .\etl-tooling\functions\check_errors.bat. Alternatively calling call .\etl-tooling\activate.bat stores the location of the functions folder in the %functions% variable so that subsequently the scripts in the functions folder can be called by call %functions%\check_errors.bat. This script will not overwrite variables that are already set.

The executables in the programs folder can either be called directly or by their respective variables. Calling call .\etl-tooling\programs\executables.bat or call .\etl-tooling\activate.bat will store the relevant executables in various variables (e.g. %curl_exe%). This script will not overwrite variables that are already set.

Calling call .\etl-tooling\functions\folder.bat or call .\etl-tooling\activate.bat will set many of the variables used in CWTS data pipelines (like %sql_src_folder%) relative to the %root_folder% variable. This is done to maintain a consistent project structure across different data pipelines. This script will not overwrite variables that are already set and will propagate sub folder variables relative to their parent-folder variables if they are already set.

When writing new pipeline code or ETL-tooling functions, the functions\variable.bat script can provide some error handling by checking the value of variables, existence of files or folders and functions parameters.

Changelog

Functions

function version
add_extended_properties v1.0.0
apply_page_compression v1.0.0
archive_pipeline v1.1.0
aws_download_folder v1.0.0
bcp_data v1.0.2
check_errors v0.4.0
classification_create_classification v1.0.0
classification_create_labeling v1.0.0
classification_create_vosviewer_maps v1.0.0
classification_load_vosviewer_maps v1.0.0
classification_optimize_np_labels v1.0.0
clean_pipeline v1.0.0
create_database v3.0.0
credentials dev
curl_download_file v1.3.0
echo v1.0.0
executables v1.1.1
extract_noun_phrases v1.0.0
folder v1.0.6
get_datetime v1.0.0
generate_database_documentation v0.1.0
grant_access_cwts_group v2.0.0
json_analyze_data v1.0.0
json_parse_data v1.1.1
load_database v1.0.0
log_runtime v0.0.1
notify v1.0.0
notify_errors v0.1.0
run_sql_folder v1.1.0
run_sql_query dev
run_sql_script v1.1.1
secret v1.0.0
set_database_file_limits v1.1.0
set_database_owner dev
shrink_database v1.0.0
split_large_files v1.0.0
split_process_files v1.0.1
split_process_months v1.0.0
split_process_numbers v0.1.0
unify_create_tables v1.0.0
unzip_file v2.1.1
unzip_folder v1.0.0
validate_database v3.0.0
validate_database_compare v1.0.2
validate_data_types v2.0.0
validate_data_types_compare v1.0.1
variable v1.0.0
wait v2.0.0
xml_analyze_data v2.1.0
xml_analyze_data_unify v1.0.0
xml_generate_prg v2.0.0
xml_parse_data v1.0.2
xml_split_data v1.0.2
zip_folder v2.0.0

add_extended_properties

apply_page_compression

archive_pipeline

  • v1.1.0
    • Fix bug in bulk folder path

aws_download_folder

bcp_data

  • v1.0.2
    • Remove wait.bat :sleep_subprocess
  • v1.0.1
    • Add wait.bat :sleep_subprocess

check_errors

  • v0.4.0
    • Add check for sql .error files
  • v0.3.3
    • %backup_log_folder% added for backup-tooling
  • v0.3.2
    • %patstat_cvt_log_folder% added for Patstat CVT
  • v0.3.1
    • Add verbose option to output file names that contain errors.
  • v0.3.0
    • %noun_phrase_extractor_log_folder% added for NBExtractorDB
  • v0.2.0
    • Download log folder added to check errors. Download log folder will contain .log and .error files from downloading data. Some programs that can write the output to this folder are for example: AWS, Azcopy and Curl.

classification_create_classification

  • v1.0.0
    • parameters
      1. db_name: classification_db_name
      2. log_folder: classification_log_folder

classification_create_labeling

  • v1.0.0
    • parameters
      1. db_name: classification_db_name
      2. log_folder: classification_log_folder

classification_create_vosviewer_maps

  • v1.0.0
    • parameters
      1. db_name: classification_db_name
      2. data_folder: classification_data_folder
      3. log_folder: classification_log_folder

classification_load_vosviewer_maps

  • v1.0.0
    • parameters
      1. db_name: classification_db_name
      2. data_folder: classification_data_folder
      3. sql_log_folder: classification_log_folder
      4. bcp_log_folder: classification_log_folder

classification_optimize_np_labels

  • v1.0.0
    • parameters
      1. db_name: classification_db_name
      2. data_folder: classification_data_folder
      3. log_folder: classification_log_folder

clean_pipeline

create_database

  • v3.0.0
    • function does not run grant_developer_access.sql automatically
  • v2.0.0
    • %db_filegrowth% is now an optional global value which defaults to 5Gb

credentials

curl_download_file

  • v1.3.0
    • Send signal after downloading
    • Add quotes to deal with ampersands in the URL
    • Remove check number of variables to deal with ampersands in the URL: The %* construction expands the variable, which exposes the unescaped &
  • v1.2.0
    • Download the header files of the file by default.
  • v1.1.0
    • Add optional %curl_header% parameter to add '-H' arguments to curl.

echo

executables

  • v1.1.1
    • bugfix: check folder failed, so executable variables were not overwritable by settings.bat.
  • v1.1.0
    • rename %read_data_exe% to %readdata_exe%

extract_noun_phrases

  • v1.0.0
    • Bugfixes
    • Tested with Scopus database.

folder

  • v1.0.6
    • add %publicationclassification_log_folder%
    • add %publicationclassificationlabeling_log_folder%
  • v1.0.5
    • add %bulk_folder%
    • add %developement_folder%
  • v1.0.4
    • add %large_file_splitter_log_folder%
  • v1.0.3
    • add %generate_prg_file_data_folder%
  • v1.0.2
    • add %read_json_tags_data_folder%
  • v1.0.1
    • add %files_data_folder%

get_datetime

generate_database_documentation

grant_access_cwts_group

  • v2.0.0
    • Add required global variable %sql_cwts_group%

json_analyze_data

json_parse_data

  • v1.1.1
    • Remove wait.bat :sleep_subprocess
  • v1.1.0
    • Add optional global variable %json_parser_source_file_extension% to filter the file type that the json parser uses as input
  • v1.0.1
    • Add wait.bat :sleep_subprocess
  • v1.0.0
    • The value of %erase_previous% should be set to erase_previous instead of true

load_database

  • v1.0.0
    • it is advised to change calls to run_sql_folder
      • be advised that copy_identity_tables functionality is not implemented
  • v1.0.0
    • rename :all_scripts to :run_all_scripts
    • rename :pre_processing_scripts to :run_pre_processing_scripts
    • rename :table_scripts to :run_table_scripts
    • rename :post_processing_scripts to :run_post_processing_scripts
    • rename function load_relational_database.bat to load_database.bat

log_runtime

notify

notify_errors

run_sql_folder

  • v1.1.0
    • By default the function will now stop running scripts when a script produces an error in its error file.
    • add global variable sql_interrupt_on_error
      • set to "false" to disable the interruption

run_sql_query

run_sql_script

  • v1.1.0
    • properly separate error and log output: errors will be written to an .error file, messages and query results will be written to the .log file.
  • v1.0.1
    • bugfix: sql variables were not being passed to script.

secret

set_database_file_limits

  • v1.1.0
    • add two additional optional parameters %mdf_file_limit% and %ldf_file_limit%. If called without these parameters, the function will set the limits automatically based on file-size (previous behavior). If called with these parameters, explicit file-limits will be set.

set_database_owner

shrink_database

split_large_files

split_process_files

  • v1.0.1
    • creating folder is no longer shown in terminal window
  • v1.0.0
    • add argument 'include_parent_directory' to take over folder structure.
  • v0.2.3
    • fix bug in amount of files check, which made the check run very long in case of a large number of files.
  • v0.2.2
    • use write-verbose powershell option to provide more information during splitting.
  • v0.2.1
    • order variable is optional
  • v0.2.0
    • ordersize and none tested.
    • functions from functions\split_process_files_size.bat ported to functions\split_process_files\split_process_files.ps1.
    • add argument 'order' to pick processing method.
    • update functions\split_process_files.bat to function as a wrapper for functions\split_process_files\split_process_files.ps1.

split_process_months

split_process_numbers

unify_create_tables

unzip_file

  • v2.1.1
    • create target_folder
  • v2.1.0
    • add optional global parameter 'zip_args'
    • replace 7zip '-y' parameter with '-aou' to not ever overwrite conflicting files.
  • v2.0.0
    • add argument 'zip_log_folder'
  • v1.0.0
    • add argument 'keep_folder_structure'

unzip_folder

validate_database

  • v3.0.0
    • remove %target_file% parameter, now auto-generated from %db_name% and %validation_data_folder%
  • v2.0.0
    • change ; separator to <tab> separator.
    • change output file to .tsv instead of .csv
  • v1.0.0
    • rename function validation_database.bat to validate_database.bat

validate_database_compare

  • v1.0.2
    • add file picker if validation files cannot be found

validate_data_types

  • v2.0.0
    • remove %target_file% parameter, now auto-generated from %db_name% and %validation_data_folder%

validate_data_types_compare

  • v1.0.1
    • add file picker if validation files cannot be found

variable

wait

  • v2.0.0
    • Remove :sleep_subprocess subfunction
    • Refactor ro remove dependency on waitfor
      • The new system uses a signal file instead of waitfor signals. Subprocesses write a line to the file, the main process counts the number of lines.
      • More robust, quicker, no more need for :sleep_subprocess, :sleep, or %sleep_timer%
      • :sleep is retained for backwards compatibility.
  • v1.1.1
    • Fix bug where '%number_of_processes%' did not get checked
  • v1.1.0
    • Add :sleep and :sleep_subprocess sub-functions

xml_analyze_data

  • v2.1.0
    • rename variables and modify documentation for xml_analyzer 0.2.0
    • frequency_ prefix removed

xml_analyze_data_unify

xml_generate_prg

  • v2.0.0
    • changes for xml_analyzer 0.2.0
    • now takes a single input file
    • program_output_folder and program_output_file_name replaced by output_file

xml_parse_data

  • v1.0.2
    • Remove wait.bat :sleep_subprocess
  • v1.0.1
    • Add wait.bat :sleep_subprocess
  • v1.0.0
    • The value of %erase_previous% should be set to erase_previous instead of true.
    • The value of %erase_xml% should be set to erase_xml instead of true.

xml_split_data

  • v1.0.2
    • Remove wait.bat :sleep_subprocess
  • v1.0.1
    • Add wait.bat :sleep_subprocess
  • v1.0.0
    • Replace erase_split argument with xml_file_splitter_remove_original_files global variable.
  • v0.1.0
    • Add missing parameters
      • %xml_file_splitter_find_files_recursive%
      • %xml_file_splitter_fix_crossref_namespaces%
      • %xml_file_splitter_order_mixed_values%
      • %xml_file_splitter_include_xml_prolog%
      • %xml_file_splitter_remove_original_files%
      • %xml_file_splitter_showoutput%

zip_folder

  • v2.0.0
    • add argument 'zip_log_folder'

Deprecated Functions

classification

  • split up into functions
    • classification_create_classification
    • classification_create_labeling
    • classification_create_vosviewer_maps
    • classification_load_vosviewer_maps
    • classification_optimize_np_labels

split_process_size

  • Deprecated in favor of split_process_files with order=preserve

xml_generate_sql_bcp_scripts

  • Merged function into xml_parse_data

Programs

program version
7z.exe system
Curl.exe 7.76.1
DatabaseDocumentatieGenerator.exe 2.1.0.0
JsonAnalyzer.exe 1.1.0.0
JsonParser.exe 1.40.0.0
LargeFileSplitter.exe 1.0.0.0
NPExtractorDB.jar 21-07-2021
ReadData.exe 4.7.0.0
VOSviewer.exe 1.6.20
XmlAnalyzer.exe 0.2.0
XmlFilesplitter.exe v1.0.0.7
java.exe system
matlab.exe system
publicationclassification-32bit.jar 1.0.0
publicationclassification-64bit.jar 1.1.0
publicationclassificationlabeling.jar 1.0.0