How do I copy a full Data Flow Task (DFT) from one project to another if pasting the project connection manager throws the error:

At least one connection manager could not be pasted correctly.

The connection manager with the name 'MyConnMngr' could not be pasted successfully.

The connection manager could not be created.

Object reference not set to an instance of an object.

(Microsoft.DataTransformationServices.Design)

Here is the full copy of the message. The link is dead:

TITLE: Microsoft Visual Studio
------------------------------

The designer could not paste one or more connection managers.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%C2%AE%20Visual%20Studio%C2%AE&ProdVer=15.9.28307.2094&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=PasteComponentsFailureCause_couldNotRestoreConnectionManagers&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

At least one connection manager could not be pasted correctly.
The connection manager with the name 'MyConnMngr' could not be pasted successfully.
The connection manager could not be created.
Object reference not set to an instance of an object. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%C2%AE%20Visual%20Studio%C2%AE&ProdVer=15.9.28307.2094&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=RestoreConnectionManagers_AtLeastOneRestoreConnectionManagerErrorOccured&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

If it was just this connection manager that I would have to build from scratch again, I would not take the time for this question. But this error stops pasting the whole DFT, and I cannot even copy and paste any boxes step by step.

The only thing that was pasted from the whole DFT was one Flat File Connection Manager for the package. It seems as if connection managers for the project cannot be pasted. I tried it in two projects. I know that sometimes objects are still files in the project and not shown in the project explorer. Such files can block pasting new objects of the same name, or you would get a warning to replace it at least. Therefore, I pasted the DFT in a project that had not had any connection manager of that name or function before, and still, the error came up.

I work in an older SSIS version from 2016. Perhaps a younger version can deal with it, but I doubt it.

The connection manager writes to a table. Perhaps once a week, the error is thrown:

Connection is busy with results for another command

SSIS get deployed to Microsoft SQL Server 2016 (SP3-CU1-GDR) (KB5040944) - 13.0.7037.1 (X64).

I know from MARS (Multiple Active Result Sets) - Microsoft Learn that the missing "MARS = True" leads to the error. But if I change "MARS" to "True" in the properties of the connection manager, the "MARS" setting changes back to "False" as soon as I click "OK" so that the bold "True" is changed to thin "False" again when I open the properties afterwards:

I also checked whether just opening the connection manager's properties triggers the "False": I saved the change without opening the properties and then checked the Git log, which showed that the MARS setting change to "True" vanishes right after the click on "OK". What it saved were the default settings for retries (ConnectRetryCount = 1), and I wonder whether allowing a higher number of retries might help here as well. I did not try it, though.

I also wonder whether forcing the settings by changing it in the xml code instead of Visual Studio might help, but I fear that this might lead to worse things than a package that I need to run by hand once in a while. It might be worth a try, though, but I would rather get some insight from others before I check weird things.

Why does the "MARS" = "True" setting jump back to "False" right away? How can I change it to "True"? Should I do it at all if something like this happens?

All columns of the INFORMATION_SCHEMA.COLUMNS view are listed at Learn -> SQL -> SQL Server -> COLUMNS (Transact-SQL)::

COLUMN_NAME is of data type nvarchar(128):

But there is no more metadata about this column. Which collation does it have?

Where is the documentation for all of the attributes of this column:

• on the Microsoft sites?

• or perhaps in some SQL Server menu itself? I have Server Management Studio. With DBeaver on a MySQL database, you can check the properties of any view or table with a right click so that you can see all of the column attributes. But MS SQL Server does not have this.

• Or how can I ask for the INFORMATION_SCHEMA.COLUMNS view of the INFORMATION_SCHEMA.COLUMNS view? Something like:

  select * from INFORMATION_SCHEMA.COLUMNS where c.TABLE_SCHEMA = 
  'INFORMATION_SCHEMA' and c.TABLE_NAME = 'COLUMNS'
  

I am not sure whether this fits to Server Fault Stack Exchange, and it has already been closed on Super User Stack Exchange. It is a problem of Firefox, not of GCP.

GCP panes do not show any filled items in any of the GCP projects in Firefox normal mode, while all of the items are shown in Firefox incognito mode and in Google Chrome normal mode. How to get the GCP items back in Firefox normal mode?

Examples

For example, checking the Google Cloud Platform (GCP) Logging is just empty:

Or the GCP Cloud Functions, just 0:

while I can see everything when entering Firefox in incognito mode where I have to login with email and password.

Tried

Tried in normal mode:

• signed out and in again
• checked it in a new tab
• checked it in a new window
• checked everything after a machine restart

Removing the Google cookies did not work either

I also removed the cookies of www.google.com and signed in again:

Yet, I still do not see any items, though I can see them with the same login in private browsing.

I found an old code on a legacy server that writes from a dataset of roughly 20 columns and >60K rows item by item to an Excel file using openpyxl. It does this by filling each cell on its own (the slow way, but not that slow either since the whole thing is done after a few minutes, and with formatting of the cells):

cell = ws.cell('%s%s' % (col_idx, i + 3))
cell.value = unicode(value).encode("utf-8")
get_style(cell, "content", column_colors[col_id])

At roughly 59K rows, it crashes, the console prints:

Killed

The logs show:

Received SIGTERM, shutting down.

The SIGTERM hints at too little memory so that the server kills the task "from outside". It is a virtual machine. Checking the memory with the command free shows that all of the free 3.5 GB get used by writing that small dataset (20 MB on the server) to Excel.

I checked it on the run and found: 500 MB just to configure the file, 700 MB every 10K rows. Writing the dataset leads to:

60 x 700 + 500 = 4700 MB free RAM needed, and the VM has only 3.5 GB. Which should be enough anyway for such a small file in the output. The output in the end is roughly a 20 MB workbook. The output Excel version should be 2007 or 2010, since it is running on Python 2.73 and in outdated legacy code.

Why does writing to a 20 MB workbook with Python's openpyxl module eat Gigabytes of RAM?

Writing the query in the GCP Logs Explorer with a regular expression (RegEx) as the filter:

I need to filter the query_name for any string that has the word ¨stat" in it.

Tried to no avail:

logName="projects/my_project/logs/my_env-production"
labels.query_name RLIKE "stat"

logName="projects/my_project/logs/my_env-production"
labels.query_name LIKE "%stat%"

logName="projects/my_project/logs/my_env-production"
labels.query_name="%stat%"

The output is No data found on the left pane, and the output list is empty.

The errors

Terminal entry not found in terminfo

and

Error opening terminal: alacritty

happen to me on a legacy server when I ssh'ed to it like:

ssh MY_USER@MY_SERVER

and use Alacritty to open text files with vi MY_TEXT_FILE.

The following is just a warning that does not keep me from working with the file in vim, but starts with these lines whenever I look up a vim file:

E558: Terminal entry not found in terminfo
'alacritty' not known. Available builtin terminals are:
    builtin_amiga
    builtin_beos-ansi
    builtin_ansi
    builtin_pcansi
    builtin_win32
    builtin_vt320
    builtin_vt52
    builtin_xterm
    builtin_iris-ansi
    builtin_debug
    builtin_dumb
defaulting to 'ansi'

The other error Error opening terminal: alacritty. is worse since you cannot run some commands then, happens to me with htop to see all of the pids.

The two errors come up likely for the same thing. How to get rid of them?

In a time-series chart in Grafana, I try to mark a bunch of labels so that all of their curves are shown.

As a default, I get only four labels' curves in the graph, but I have dozens of labels and I do not want to mark everything with the mouse, it takes too much time and nerves.

The idea is likely that choosing too many curves will leave you lost in the lines. But in this case, the graph is about finding outliers, strong changes and trends, or just high numbers. You can hover over any curve that might catch your eye, and that is all. Thus, having 80 curves in one graph is no problem.

The filter is just about shrinking the list, not about marking all labels in it. I can use it to Regex-check for queries with 2-digit seconds duration and some other filter on the query_name.

Yet, I just want to see all labels' curves in one go, and not just by clicking like in the following:

Is there a trick to get this done? Perhaps even by using the Grafana Dashboard code to mark the jobs as a hardcoded list? Or is there a shortcut or other trick to pick all?

I need LBMs on Google Cloud Platform of metric type "Distribution" to make time series charts with values like counts or duration for many labels in one go.

Example: for 10 different steps in the data workflow counts and duration of the day are logged in a dictionary and logged as jsonPayload using gcloud logging's log_struct().

Although I see logged events when looking at a month in the Logs Explorer, I cannot choose any of the jsonPayload items in the drop-down menu to create the needed Log-Based Metrics (LBM) from it so that I can choose them in Graphana to show graphs.

In this drop-down menu, I was expecting my key-value pairs that I logged during the run of a cloud function, but I cannot choose anything that was logged, I only see other jsonPayload system entries. This is how the logs look like.

Side-note: I save the jsonPayload with a middle branch in that the children are under one middle branch in the drop-down, for example jsonPayload.json_metadata.rows_count and not jsonPayload.rows_count. That makes it clearer what is coming from the Python logging. I do not think that this is a problem, but you never know. I cannot find this middle branch and its children in the drop-down menu so that I cannot make the needed LBMs. How to get a full drop-down menu for the "Field name"?

I need the jsonPayload in the logs of a Google Cloud Function instead of the textPayload. My aim is to use the keys of the dictionary as labels (see Log-based metrics labels) for Log-Based Metrics so that these can be addressed in Grafana.

I am using Python's logging module, but I can also switch to something else if needed.

I need as output in the logs:

jsonPayload: `{'key1':value1, 'key2':value2}`

But I get a textPayload output instead, the whole next line is a string:

"2022-02-08 15:43:32,460 [INFO]: {"key1": value1, "key2": value2}"

Real example from the logs, in the middle, you see the textPayload:

The picture as text:

{
insertId: "000000-1b431ffd-e42d-4f83-xyz"
labels: {1}
logName: "projects/MY_PROJECT/logs/cloudfunctions.googleapis.com%2Fcloud-functions"
receiveTimestamp: "2022-02-08T15:43:41.808217166Z"
resource: {2}
textPayload: "2022-02-08 15:43:32,460 [INFO]: {"json_metadata": {"countrows": 736203, "countcolumns": 6, "size": 48261360, "gcs_stamp": "2022-02-08 15:43:32.451000+00:00", "python_stamp": "2022-02-08 15:43:31.055538"}}"
timestamp: "2022-02-08T15:43:32.460Z"
trace: "projects/MY_PROJECT/traces/dd97759176248586a3d3xyz"
}

First tries

Reading from https://cloud.google.com/logging/docs/structured-logging:

In Cloud Logging, structured logs refer to log entries that use the jsonPayload field to add structure to their payloads. Structured logging applies to user-written logs.

I tried to get this "structured logging" following Writing structured logs by

logging.info(json.dumps(json_for_gcp_lbm))

but to no avail.

Further in the links: there is a built-in Logging agent from GCP that uses fluentd as to About the Logging agent seems to be available only for Google Kubernetes Engine or the App Engine, not in a Google Cloud Function:

If you're using Google Kubernetes Engine or the App Engine flexible environment, you can write structured logs as JSON objects serialized on a single line to stdout or stderr. The Logging agent then sends the structured logs to Cloud Logging as the jsonPayload of the LogEntry structure.

How can I get the jsonPayload in this output?

I get the warning

OpenBLAS WARNING - could not determine the L2 cache size on this system, assuming 256k

in the logs of the "LOGS" tab of the Cloud Function.

There is already a Q/A on this on Stack Overflow at AppEngine warning - OpenBLAS WARNING - could not determine the L2 cache size on this system, but it is not asked for a Google Cloud Function, only for the Google App Engine.

I wonder how I can get rid of this warning in a Google Cloud Function and whether I should care at all?

• It is just a warning anyway and the quite heavy Cloud Function (with a lot of calculations + saving 50 MB csv, 700 MB needed RAM, 1 GB allocated, 540s timeout time) runs through anyway. Doing nothing can be an accepted answer.
• Perhaps I can find the L2 cache size somewhere in the settings, which system is meant here?

I get this error FileNotFoundError: [Errno 2] No such file or directory when I try to write a csv file to the bucket, using a csv writer that loops over batches of data. The full insight into the Cloud Function logs around that error:

File "/workspace/main.py", line 299, in write_to_csv_file with
open(filepath, "w") as outcsv: FileNotFoundError: [Errno 2] No such
file or directory: 'gs://MY_BUCKET/MY_CSV.csv'

Function execution took 52655 ms, finished with status: 'crash' 

OpenBLAS WARNING - could not determine the L2 cache size on this
system, assuming 256k  ```

And this, although this bucket_filepath definitely exists: I can upload an empty dummy file and get its "gsutils URI" (right-click on the three dots at the right side of the file) and the bucket_filepath will look the same: 'gs://MY_BUCKET/MY_CSV.csv'.

I checked saving a dummy pandas dataframe instead using pd.to_csv and it worked with the same bucket_filepath (!).

Therefore, there must be another reason, likely the writer is not accepted, or the with statement that opens the file.

The code that throws the error is as follows. It is with the same code working outside of Google Cloud Function in a normal cron job on a local server. I have added two debug prints around the line that throws the error, the print("Right after opening the file ...") does not show up anymore. The subfunction query_execute_batch() that write_to_csv_file() is calling for each batch is also shown but likely not the problem here since the error happens already at the very start when write-opening the csv file.

requirements.txt (which are then imported as modules):

SQLAlchemy>=1.4.2
google-cloud-storage>=1.16.1
mysqlclient==2.1.0
pandas==1.2.3
fsspec==2021.11.1
gcsfs==2021.11.1
unicodecsv==0.14.1

And from the main.py:

def query_execute_batch(connection):
    """Function for reading data from the query result into batches
    :yield: each result in a loop is a batch of the query result
    """
    results = execute_select_batch(connection, SQL_QUERY)
    print(f"len(results): {len(results)}")
    for result in results:
        yield result

def write_to_csv_file(connection, filepath):
    """Write the data in a loop over batches into a csv.
    This is done in batches since the query from the database is huge.
    :param connection: mysqldb connection to DB
    :param filepath: path to csv file to write data
    returns: metadata on rows and time
    """
    countrows = 0
    print("Right before opening the file ...")    
    with open(filepath, "w") as outcsv:
        print("Right after opening the file ...")        
        writer = csv.DictWriter(
            outcsv,
            fieldnames=FIELDNAMES,
            extrasaction="ignore",
            delimiter="|",
            lineterminator="\n",
        )
        # write header according to fieldnames
        writer.writeheader()

        for batch in query_execute_batch(connection):
            writer.writerows(batch)
            countrows += len(batch)
        datetime_now_save = datetime.now()
    return countrows, datetime_now_save

Mind that for the above script to work, I import gcsfs which makes the bucket read-write-available. Else I would likely need a google cloud storage object like for example:

storage_client = storage.Client()
bucket = storage_client.bucket(BUCKET_NAME)

and then make the file in that bucket with further functions, but that is not the aim here.

In the following, the pd.to_csv code that works, it uses the output of a dummy SQL query SELECT 1 as the input of a dataframe. This can be saved to the same bucket_filepath, of course the reason might not just be pd.to_csv() as such, but also that the dataset is a dummy instead of complex unicode strings from a huge SELECT query. Or there is another reason, I am just guessing.

if records is not None:
    df = pd.DataFrame(records.fetchall())
    df.columns = records.keys()
    df.to_csv(filepath,
        index=False,
    )
    datetime_now_save = datetime.now()
    countrows = df.shape[0]

I would like to use the csv writer to have the chance to write in unicode with the unicodecsv module and the chance to use the batches.

I might be willing to change to batches (loop + append mode or chunksize) in pandas like in Writing large Pandas Dataframes to CSV file in chunks to get rid of this bucket filepath problem, but I would like to rather use the ready code (never touch a running system).

How can I get the saving of that csv done with the csv writer so that it can open a new file in the bucket in write mode = with open(filepath, "w") as outcsv:?

The given function write_to_csv_file() is just a tiny part of the Cloud Function which uses a wide range of functions and cascaded functions. I cannot show the whole reproducible case here and hope that it can be answered by experience or easier examples.

Trying to make a synchronous pipeline, I need to copy a csv file from Google Cloud Storage after it has been saved in Google Cloud Storage. The copy job does not have to be triggered right after the saving, it can also happen within some time frame at least. It just may not happen before the file has been saved. Therefore, either a trigger event or a cronjob are possible, or you may suggest something else.

How can I trigger copying a chosen csv file after it has been saved in Google Cloud Storage? Can I use a Cloud Function to do the copy job or are there other ways?

I need something like Cloud Storage for Firebase: download metadata of all files, just not in Angular but in Python and just for a chosen file instead.

The aim is to return this information when the Cloud Function finishes with the return statement or just to log it during the run of the Cloud Function as soon as the file is saved in the Google Storage bucket. With that information at hand, another job can be started after the given timestamp. The pipeline is synchronous.

I have found Q/A's on loading a file or its data into the Cloud Function

• using /tmp directory like at Reading Data From Cloud Storage Via Cloud Functions
• or load the data (and not the file) using storage.Client() or pandas df.read_csv() like at How to load a file from google cloud storage to google cloud function

to extract data stats into the running Cloud Function from the external file.

Since I do not want to save the large file or its data in memory at any time only to get some metadata, I want to download only the metadata from that file that is stored in a bucket in Google Storage, meaning timestamp and size.

How can I fetch only the metadata of a csv file in a Google Cloud Storage bucket to the Google Cloud Function?

I follow the guide at timescaleDB for APIs with Certificate authentication --> 2. Generate keys and certificates for nodes:

For an SSL connection, you need four environment variables:

1. SSL Root Certificate
2. SSL Certificate (for the node)
3. SSL Key (for the node)
4. SSL Password (for the node)

As to 1.), you get it from timescale when starting a database as a text or as a download.

As to 2.), you seem to be able to create it yourself as a self-signed certificate with:

openssl req -out server.csr -new -newkey rsa:2048 -nodes \
-keyout server.key

I filled "country" and skipped the rest of the entries only with "." (this was suggested for skipping). I also filled the "challenge password". Not sure how much relevant it is to fill this and whether the password is 4.) in the list above or not.

As to 3.), you first need to change your openssl.cnf which is not mentioned in the timescale guide. That is done by searching with whereis openssl.cnf, or in my case, the location was shown in the terminal output when the parameters were still wrong in there, which showed /usr/lib/ssl/openssl.cnf as the location. Change the config with sudo nano /usr/lib/ssl/openssl.cnf so that the private key “server.key” and the root certificate “ca.pem” are found. After this, run the command of the guide:

openssl ca -extensions v3_intermediate_ca -days 3650 -notext \
-md sha256 -in server.csr -out server.crt

but then, you get the error:

Using configuration from /usr/lib/ssl/openssl.cnf
CA certificate and CA private key do not match
140718372717888:error:0B080074:x509 certificate routines:X509_check_private_key:key values mismatch:../crypto/x509/x509_cmp.c:299:

The script still creates server.key and server.csr, but the needed server.crt cannot be created.

And only How to convert .csr to .cer (or whatever usable on Windows) seems to be able to help me out to get the server.crt with:

openssl x509 -req -in server.csr -signkey server.key -out server.crt

Entering this in the SSL connection settings at Hasura:

But then, when trying to connect to my database at timescale using Hasura, I get the error Missing Env Var:

Adding data source failed
Inconsistent object: Missing Env Var: -----BEGIN CERTIFICATE----- MIICoTCCAYkCFAHx6x6kVHJ023/ui0aLoWLqA8RyMA0GCSqGSIb3DQEBCwUAMA0x CzAJBgNVBAYTAkRFMB4XDTIxMDkyMTE4MDQzNVoXDTIxMTAyMTE4MDQzNVowDTEL MAkGA1UEBhMCREUwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDWGxsz
…
g98irlkrtDn17jIzmZMjlJEQ75P9L78lJMT2eYCFRqVoCm+7x5IUS18UlnGUtwR4 BuIZOg4= -----END CERTIFICATE-----

I guess that this is just a follow-up problem as the CA certificate and the CA private key obviously do not match? Therefore, the question is mainly about CA certificate and CA private key do not match, instead.

Answer timescale:

While we provide the CA root because the SSL Certs for each service are internally signed, so this will allow you to create trust, but we do not provide the private keys or SSL passwords for them.

The documentation you linked to is for creating certificate auth between nodes in an on-premise multi-node environment. Timescale cloud does not allow for certificate auth, so they wouldn't be relevant here, and you wouldn't be able to combine certificates you create with the CA you downloaded from the cloud portal.

As a workaround, you can also drop the SSL encryption since it is only optional:

simply providing the entire connection string (as copied from the Service URI field in the Timescale Cloud Portal) was sufficient to connect.

I tested this, it is true, you can run it without SSL although SSL is prepared to be used. I used the root certificate to SSL-connect with psql or pgAdmin to the PostgreSQL timescaleDB backend, and they only asked for the SSL root certificate --> 1.) above, and not for node CA certificate and node CA private key --> 2.) - 4.) above.

Connecting with just the SSL root certificate also worked with node-postgres / react-postgres / Express / React App, see:

• How to create a pool object in javascript that can connect to a database backend using SSL?
• TypeError: Cannot read property 'rows' of undefined
• "Cannot PUT /mytable" when running UPDATE SQL command from react-postgres "React App" localhost:MYPORT on PostgreSQL / node.js backend

Yet, the Hasura Application Programming Interface settings ask four SSL connection environment variables of the list at the top, how can I get them so that I can connect to Timescale?