Author name: cndro

The Wix Data API allows you to work with your database collections using code and gives you more capabilities than what is available in the Editor alone. You can use this API to work with your site’s registered users. This API lets you get information about the current user, prompt a user to log in, and can log out the current user. The Wix-users API can be used in your site’s front-end code. Here is the list of the processes we would follow in this article to get data from the Wix API: Create an application under the Wix developer center Set up permission and derive the token, authorization, code, access token, and refresh token Get the data you need   Create an application under the Wix Developer center Step 1: To get started with the API, we need to first go to the website https://dev.wix.com/ and create a new application via the developer center for our Wix account. Afterward, we would set our Redirect URL & App URL on the OAuth tab. Step 2: The next step is to add new permission under the permissions tab, where we select the kind of permission we need from the different categories (Wix Stores, Pricing Plans, Marketing, Wix Events, Contacts & Members, etc.). Any of these permissions we set, actually indicates the type of data we are interested in. Step 3: After this has been done, we will test our app. It will prompt us to install the app and redirect us to the App URL we defined. From here we will generate a token code under the URL params returned. We’ll then proceed to authenticate by using the token code we obtained alongside other credentials available.   For Authentication In getting the authorization code and access token, we can use the Flask framework in form of a web application to generate the details we need. We’ll use the authentication URL endpoint with the token code, appId, and Redirect URL as a connection string. When the flask app has been ran, it will generate our authorization code.     from flask import Flask,redirect,url_for     import config import requests app = Flask(__name__) app.config.from_pyfile(“config.py”, silent=True) import json @app.route(‘/callback’) def oauth_callback(): redirect_url = ‘https://cndro.com/’ auth_url = (‘https://www.wix.com/installer/install?’ + ‘token=’ +app.config[“TOKEN_CODE”]+ ‘&appId=’ +app.config[“APP_ID”]+ ‘&redirectUrl=’ + redirect_url) return redirect(auth_url)   Generate Access Token and Refresh TokenTo generate the Access token and Refresh token, we’ll use the temporary authorization code returned, together with the URL endpoint and other credentials to request an access token and a refresh token.   @app.route(‘/access’) def wixAccessToken(): the_url = ‘ https://www.wix.com/oauth/access’ response = requests.post(the_url, headers = { ‘Content-Type’: ‘application/json’ }, data = { ‘grant_type’ : ‘authorization_code’, “client_id”: app.config[“APP_ID”], “client_secret”: app.config[“APP_SECRET”], “code”: app.config[“AUTH_CODE”] }) response_data = json.loads(response.text) return response_data   We must note that this access token expires in 5 minutes, because of this we must generate a new access token from the refresh token obtained. We’ll use the same URL endpoint we used earlier and also indicate our grant type as ‘refresh_token’.   @app.route(‘/refresh’) def NewAccessToken(): the_url = ‘ https://www.wix.com/oauth/access’ response = requests.post(the_url, headers = { ‘Content-Type’: ‘application/json’ }, data = { ‘grant_type’ : ‘refresh_token’, “client_id”: app.config[“APP_ID”], “client_secret”: app.config[“APP_SECRET”], “refresh_token”: app.config[“REFRESH_TOKEN”] }) response_data = json.loads(response.text) return response_data if __name__ == ‘__main__’: app.run(host=’localhost’,port=5000)   We can get data from any of the permissions categories we added by using its URL endpoint and with our Access token. You can as well use other HTTP Methods apart from GET such as POST, GET, PATCH, DELETE with any of the endpoints you’re interested in working with. An example code to get data from the Contact & Members Endpoint def getListContact(): the_url = ‘ https://www.wixapis.com/contacts/v4/contacts ? paging.limit=1000&paging.offset=1000’ response = requests.get(the_url, headers = { ‘Authorization’: wixAccessToken() }) response_data = json.loads(response.text) return response_data print(getListContact())   Follow the steps above and you will have your data extracted from Wix API. If you enjoyed this post, share it with your friends and colleagues. Connect with us on Twitter, Instagram, and YouTube. Thanks and see you next time

Azure blob storage is a Microsoft Azure feature that allows users to store a large amount of unstructured data. Azure blob storage can be used as a Delta Lake where you keep all the data that belongs to your organization. Blob is an acronym and it stands for Binary Large Object which includes images and multimedia files. In this article, I will show you how to connect to Azure Blob storage from Alteryx. Firstly, let’s connect with the files in the Azure container from Alteryx designer but before we can do that, we need to generate a Shared Access Signature (SAS) code for each file we would like to connect to. We are going to use wix.csv data as sample data. We are going to follow these steps below; Go to https://portal.azure.com and search for Azure Storage. Create a container (I named it alteryxtestcsvfiles) on the Blob section. Click on the three ellipses to the right of the window.     2. Click on Generate SAS     3. Adjust the expiry date to a future date   4. Click on Generate SAS token and URL     Successfully followed the four steps? Good job! Now use the SAS Code you generated to connect to the data from Alteryx download tool. With the SAS Code, we can now communicate with the resources privately. . . https://samplestorageyoucreated.blob.core.windows.net/alteryxtestcsvfiles/yourdata.csv?sp=r&st=2021-07-01T18:29:53Z&se=2021-07-06T02:29:53Z&spr=https&sv=2020-02-10&sr=b&sig=SsCdhhH%2FZG6qgeCEyAzvsuj0aoIZrS2qyhwyPenlsrk%3D The SAS code is split into two parts, we have Field1 and Field2. Field1 = https://samplestorageyoucreated.blob.core.windows.net/alteryxtestcsvfiles/yourdata.csv Field2 = sp=r&st=2021–07–01T18:29:53Z&se=2021–07–06T02:29:53Z&spr=https&sv=2020–02–10&sr=b&sig=SsCdhhH%2FZG6qgeCEyAzvsuj0aoIZrS2qyhwyPenlsrk%3D The view below shows the complete Alteryx workflow to get our CSV data, transform the data and use the browse tool to show the CSV data.       The Azure Blob Storage URL and the query string serve as the input to the workflow. Use the input tool as shown in the screenshot below.     Now we will concentrate on the Alteryx download tool, the configuration interface. I brought the Alteryx Download tool into the workflow. Take a look at the configuration window for the basic settings of the Download tool.     The Headers window has Accept: text/plain, and also has the field1 selected as the field to take care of the response body.     The screenshot below shows using the Payload Configuration tab to get and use the query string for authentication.   The Text to Columns tools (the first and the Second) help to get the text data into their table equivalence.     Use the Select Tool to pick the Fields needed for the Dataframe by deselecting unwanted fields in the configuration window.       I use the Dynamic Renaming tool to pick up the field name of a dataset from the first row of the data.       We have successfully connected to the CSV data in Azure Blob with Alteryx with the above steps. If you enjoyed this article, share it with your friends and colleagues!    

Extensible Markup Language(XML) is a markup language that defines a set of rules for encoding documents in a format that is both human-readable and machine-readable. XML uses a series of simple tags to describe data and these tags can be created in an easier way. CSV is a simple file format used to store tabular data such as a database or spreadsheet. A CSV file stores data in a structure that can be read and written by a wide range of several spreadsheet software packages. In this article, I’ll walk you through how to convert CSV to XML in Alteryx. Let’s get right in. We will use some tools in Alteryx to achieve our aim. The first tool we will be using in this conversion is the input tool which shows an input file. This is where we will pass in our sample data. The second tool is the Record ID which will be used to number the data sequentially. We can call this an index. The third tool is the Transpose which we will use to change the data orientation from vertical to horizontal. Here is the result of this phase:   Add regex to remove spaces in the Name column using the Formula tool. After then, use the select tool to pick the fields and rename the datatype from string to integer. We will use the formula tool again to perform two different calculations for the name tag. Afterward, add both name tags and re-index using the Record ID tool. Use the text input tool to add xml initiation line such as this; <?xml version=”1.0″ encoding=”UTF-8″?>,and it will be passed to the data. The union tool will then be parsed in to connect different data coming from the workflow. The summation tool coming from the Record ID tool will be used to group the data using the name column.       The Append Field tool as shown below will be used to append some of the fields i.e. ColumnNumber to each of the fields(XmlLine, RecordId, Level). The other tools used include the Multi-Row Formula tool, the formula tool, and then parsing the join, union tool and summarizing them to obtain our XML file.       We’ll then use the output tool in viewing our results.  

Introduction Athena is a serverless query service that allows you to run SQL queries on your data stored in S3. It can easily query large datasets (or Data Lakes), whether they are structured, unstructured, or semi-structured data. A database in Athena is a logical grouping for tables you create in it. In this short guide, you will learn how to connect to CSV data in S3 bucket with Athena database. Let’s dive right in. The AWS glue crawler will help to catalog the table and give it the right schema. The steps are highlighted below.  Step 1: Open the Athena database from your AWS console and toggle to the database you want to create the table on. Click on Create table.   Step 2: Click on “from AWS Glue Crawler”. The “from S3 bucket data” is the manual process where you will enter the data columns manually. After you have clicked on the from AWS Glue Crawler you will be transferred to the AWS Glue platform for the crawler definition. Step 3: Input the name of the crawler and press the Next button. Step 4: Leave every entry at the default and enter the S3 bucket directory where the CSV file is located. Ensure you add a forward slash after the directory definition. Step 5: Choose “No” for Add another data store. Step 6: On the IAM role section, make sure you create a new role for the crawler and press the “Next” button. Step 7: After you have created the crawler, locate the name of the crawler. Select the crawler and click on run Crawler. The crawler will then create a table from the CSV file in the S3 bucket.   NOTE: A comma (,) at any point in a row of a CSV in the s3 bucket will surely disrupt the Athena data table, so make sure you replace any comma symbol in all rows of the CSV files. As a culture, I like to replace comma symbols with spaces. In this way, Athena will not return a bad table and will not split up any rows using the comma. Below is the original data from EXCEL.   The picture below shows the CSV file after being ingested from the S3 bucket to Athena Table. As you can see the data was returned as unstructured data in the Athena database. Resolution: Replace comma (,) symbols in all rows of the CSV files, then bring the data back into the S3 bucket. Then this will be perfectly provision by Athena Database because of the absence of comma symbols. That’s pretty much it. Follow the step-by-step guide to connect your CSV data in S3 to Athena database. Share your thoughts in the comment box below.

Introduction The snowflake connector is a native, pure Python package that does not depend on JDBC or ODBC. It can be installed using pip on Linux, macOS, and Windows platforms where Python 3.6 and upwards are installed. The connector supports developing applications using the Python Database API v2 specification (PEP-249), including using the following standard API objects: Cursor objects for executing DDL/DML statements and queries. How to install the Connector Python Package Installer & Setup Tools The Snowflake Connector for Python is installed with pip, first execute the following command to ensure the required version is installed: 1python -m pip install –upgrade pip To install the Snowflake Connector for Python and the dependent libraries: Determine the version of the Snowflake Connector for Python that you plan to install. To install the dependent libraries, run the pip (or pip3) command and point to the requirement for that version of the connector. 1pip install -r https://raw.githubusercontent.com/snowflakedb/snowflake-connector-python/v2.6.2/tested_requirements/requirements_36.reqs Validate your installation Create a file known as validate.py, and run the file which shows the version of the snowflakes. If this code brings up an error when ran, this shows that something is wrong with the installation. #!/usr/bin/env python import snowflake.connector # Gets the version ctx = snowflake.connector.connect( user=’john’, password=’xxxxxxxx’, account=’xxxxxxxx’ ) cs = ctx.cursor() try: cs.execute(“SELECT current_version()”) one_row = cs.fetchone() print(one_row[0]) finally: cs.close() ctx.close() How to Use the Python Connector You can Connect to Snowflake using the login parameters: import snowflake.connector PASSWORD = os.getenv(‘SNOWSQL_PWD’) WAREHOUSE = os.getenv(‘WAREHOUSE’) conn = snowflake.connector.connect( user=USER, password=PASSWORD, account=ACCOUNT, warehouse=WAREHOUSE, database=DATABASE, schema=SCHEMA ) After you have logged in, create a database, schema, and warehouse with the connection. How to Create Database, Schema, and Warehouse  You can use the CREATE DATABASE, CREATE SCHEMA and CREATE WAREHOUSE commands to create this. I displayed an example below for each, whereby I specified the name of the database in which to create the schema and also a name specified for the data warehouse. conn.cursor().execute(“CREATE WAREHOUSE IF NOT EXISTS john_warehouse”) conn.cursor().execute(“CREATE DATABASE IF NOT EXISTS testing”) conn.cursor().execute(“USE DATABASE testing”) conn.cursor().execute(“CREATE SCHEMA IF NOT EXISTS testingschema”) Using the Database, Schema, and Warehouse You can also reference your database, schema, and warehouse. Make use of each with the following commands with which we can start creating tables afterward. conn.cursor().execute(“USE WAREHOUSE tiny_warehouse_mg”) conn.cursor().execute(“USE DATABASE testdb_mg”) conn.cursor().execute(“USE SCHEMA testdb_mg.testschema_mg”) How to Create Tables You can create tables and insert data into your database using the connection. Check the  example below. onn.cursor().execute( “CREATE OR REPLACE TABLE ” “first_t(col1 integer, col2 string)”) conn.cursor().execute( “INSERT INTO first_t(col1, col2) VALUES ” + ” (123, ‘test string1’), ” + ” (456, ‘test string2’)”) Hope you found this article helpful. If you have any questions, feel free to ask in the comments section.

Introduction ETL Tools can deliver instant data integration solutions to help businesses in saving time, cost, and resources. Recently, there has been an increase in usage of Alteryx and Fivetran by companies. Both have gained a lot of attention as several companies are really adopting them. Alteryx and Fivetran have been used to solve many pressing issues in the world of Data Analytics, Data Engineering, and Data Science. Today, I would like to provide what each of these cool software does, probably you might be in a situation where you would like to know which one you would need for your business processes. Alteryx Alteryx is a drag and drop software used for data cleansing, mining, ETL, and other Business Intelligence activities. It is very easy to use. In fact, you do not need to have the technical skills to start using Alteryx. Alteryx provides you with many tools for various purposes. You can pick these tools, drag and drop them on the canvas for your extraction, transformation, and loading workloads. It also provides you with different products access for your data integration workloads which are being used for different purposes. There is a designer version of Alteryx which is being used for the development of applications and workflows. You can install Alteryx Designer on your machine and start using it to develop workflows. There is an Alteryx Server version that provides the company with the ability to publish and automate your resources. You can collaborate with your team and get feedback on the application you are building with just a few clicks in Alteryx Gallery. It’s worth noting that Alteryx is used for a variety of purposes one of which is that you can use Alteryx to work with ETL processes. You can use it to extract data from APIs, webpages, transform varieties of data, JSON, CSV, XML, and load the data into different databases or warehouses. Now let us look at each of these products. Alteryx Designer: It provides you with different tools that have different configurations. This is where development happens and as well as your playground to connect with different Alteryx resources and external data. Alteryx Designer is used by Data Analysts, Data Engineers, and Data Scientists. Interestingly all the tools needed for development by them are all in Alteryx Designer. Alteryx Server: It serves as a sharing platform; you are able to move your development to production with this powerful software. You can use macro and applications built by your co-workers and collaborate with each other on the Server. Alteryx Server gives you the ability to connect your applications with internal and external applications. Alteryx Connect: It  is a place where you keep resources like data sources connection metadata, workflows information, Analytical app information, e.t.c for easy access. I want you to see Alteryx Connect as your room wardrobe where you can easily find your array of clothes easily. With Alteryx Connect, companies can easily locate resources and keep all the Alteryx resources and packages organized. All your resources are cataloged for you. Alteryx Gallery: With Alteryx gallery, you can collaborate with your team, you can access your company’s Alteryx Gallery anywhere you want because it is a cloud-hosted or self-hosted application. Alteryx Gallery allows you to share data, macros, applications, and insights within your organization. With the Alteryx Gallery, you have the ability to manage and accommodate many more users as your business needs grow. All these users can then develop different applications and share them on the platform. Alteryx specializes in self-service analysis with an elegant user interface. These analyses can be used as Extract, Turn, Load (ETL) methods within the Alteryx system. You can use Alteryx to connect with different data sources and perform complex analysis, do spatial, predictive, and statistical analysis with just drag and drop tools. Fivetran Fivetran is a data integration platform you can use to extract, transform and Load (ETL) processes. The design orientation of Fivetran is such that it does not want you to do any manual work for your data integrations, just plug and play. Read along as I give a better illustration. I want you to think of how you pay for your electricity every month. You would not need to go to the power source itself to extract the electricity you need; you believe that all you need is to pay for your electricity and you sit back at home to watch your television. Sounds cool, right? No stress! This is exactly how Fivetran works. Even if you have the engineering capability, the time factor to get the electricity from the extraction point would wear you out. Fivetran provides you with many connectors to different data sources. For example, it provides you with connectors to Facebook Ads Insight, Salesforce, Zendesk, Twitter Analytics, and lots more. You can check Data Sources | Connector Directory | Fivetran. Interestingly, Fivetran provides you with the ability to create your custom connectors, but in some cases, you would not need to create custom connectors yourself because there are many connectors in the box already. Personally, What I like about Fivetran is that I can stream my data into Fivetran, you can choose to use either Amazon Kinesis, Apache Kafka, and others. Pricing Fivetran pricing is flexible and the costs are calculated based on your workload. There are four pricing/workload plans namely the Starter ($1 per credit), Standard ($1.5 per credit), Enterprise ($2), and the Business-Critical plan (contact support for this). In fairness, there are many things you can use this powerful software to achieve. They are powerful and they make things easy for you. I cannot tell it all to you here, the best way is to explore them. Alteryx provides you with 30 days free trial with full access to all the Alteryx Designer resources. On the other hand, Fivetran provides you with 14 days free trial as well, you can use over 150 plus connectors freely for the period of 14 days. I will advise you to try

At the beginning of my Tableau Journey back then in 2018, I was going through a goal news edition and I came across something similar to the image below. I thought about it, what if I can do something similar in Tableau with the previous function. So, I jumped onto Tableau with sample superstore to see the possibilities of this. It was surprising to see * in tooltip whenever the list of items per region exceeded 1. I was cool and happy with the screenshot above but got surprised with the screenshot below; I got that asterisk because Tableau is trying to aggregate by using (ATTR) and ATTR returns a returns value if it has a single value for all the rows else it throws it out *. I love investigating stuff, so I traced what this could be to how many products we have per state. Notice that we have 103 marks which indicate 103 products (since this is more than one, the asterisk should be expected according to its definition). Enough talking and let’s get down to the solution. With Tableau’s previous function we could build something similar to what I wanted. Here is a quick glance at Tableau’s definition of the Previous Function; returns the value of this calculation in the previous row. Calculated Field IF FIRST () =0 THEN MIN ([Product Name]) ELSE PREVIOUS_VALUE (MIN ([Product Name])) + ‘ ,’ +MIN ([Product Name]) END LAST () = 0 Tableau worksheet. Build the bar chart by dragging States to row and sales to column. 2. Build another worksheet that will be used as a tooltip in the bar chart, here is where we will use the two calculated fields. a. Create a new sheet b. Drag States to row, followed by product names, then drag the first calculated field to row. c. Compute the Table Calculation using Product Name as shown below. 3. Also drag the second calculated field to row, then compute using Product name as shown above. 4. Hold Ctrl and drag the second calculated field to filter, then check True. 5. Go back to the bar chart worksheet, click on the tooltip and insert the second worksheet you just built. 6. Hover on each bar to see if your tooltip is working fine. Then format to your taste. Now that you know how the Previous value function works in Tableau, you can go ahead to try your hands on it. If you have any more questions, feel free to ask in the comment box. Thanks for reading.

Introduction Data Warehousing is an evolving field. The old models of data warehousing such as batch processing and ETL (extract, transform, load) are inefficient for modern data-intensive applications. Data warehouses can’t handle the number of concurrent updates that occur when they’re in use. Snowflake is a cloud-computing data warehouse and you will learn how it can benefit your business in this article. What is Snowflake? Snowflake was founded in 2013 and has a global customer base of over 3,000 organizations. The data warehouse is designed for scalability and performance, providing companies with the ability to analyze their data for insights that drive business decisions. Snowflake Data Warehouse is an enterprise-grade data warehouse that is used to store massive amounts of sensitive data (like CRM) for later use by customers. Data warehouses are used in most businesses to store all their data for future use. Businesses do this to ensure that the data is not lost and can be retrieved when needed. Data warehouses are usually structured in different models with each model having its own benefits. Why You Should Use Snowflake for Your Data Storage Needs Today A Snowflake is a data warehouse based on the relational model of data storage. It is designed to deal with big data. It is the most scalable, cost-effective, and secure cloud solution for data warehousing. You should use it for storage because it can handle huge datasets that are generated by IoT devices which are bigger than what traditional databases can handle. The Architecture of Snowflake’s Data Warehouse Back in the days when we buy cable TV, we always get a package deal of both content and infrastructure. Today, we can have them separately. Now, you have control over what you use and pay for. Similarly, Snowflake separates computation and storage. Companies with high storage but low CPU benefit from this architecture. Snowflake’s architecture is made up of three layers namely: Database storage layer Compute layer Cloud services layer Benefits of Snowflake to Your Business Effective Data Sharing Snowflake’s architecture allows smooth data sharing among Snowflake’s users. Companies can also share data with consumers regardless of the consumers being Snowflake’s customers or not. A client account can be created for them directly from the user interface and managed by the provider. Accessibility and Performance Gone are the days where you could experience delays or failures due to concurrency when you use a traditional warehouse. With Snowflake’s unique multi-cluster architecture, you can run your queries concurrently without any hassle. The cloud is elastic such that if you need to run a large volume of queries quickly, you can scale up. Security Snowflake is a well-known cloud-based data warehousing platform used by governments, health, banking sectors, and security businesses. It has built-in protections that obey SOC 1 and Type 11 regulations. Simplicity Snowflake is fully cloud-based and requires no IT installation or management. It has built-in speed optimization, data security, and safe data exchange that ensures that datasets of any complexity have instant access and restoration. Low cost Snowflakes provide a range of pricing based on your usage and allow you to switch computing resources on and off. Snowflake’s low-cost, cloud-data platform allows you to store unlimited amounts of data and only pay for what you use. Additional Benefit For optimum marketing The marketing automation space is one of the most competitive because it’s so important for companies to stay ahead of their competitors in terms of making sure they are getting the most out of their marketing spending. This is where Snowflake Data Warehouse comes in. It’s the power behind marketing automation platforms in companies. By using it, they can provide marketers with a variety of data-driven tools that will help them create optimal customer journeys for each person. Conclusion Snowflake data warehouses are one of the most popular architectures for data warehousing. They provide real-time analytics, fast queries, and massive scalability which makes them more effective than other models of data warehousing.