Developers Club geek daily blog

Reefs of use of Excel Power Query and MySQL for automation of the reporting

1 year ago
image
All hi.
There came new 2016, so it is time to update tools for simplification of boring mechanical work. Departments of analytics, marketing, sales often face the following difficulties when updating the reporting:
1. Data should be aggregated from several sources.
2. Reports are formed in Excel that imposes considerable restrictions for volume of the processed data.
3. Modification of the unloadings which are in advance configured by developers business as a rule not the faster.

If reports need to be updated weekly or even daily, then this procedure becomes very napryazhny even for the most patient. By means of a superstructure of Excel Power Query and data record in MySQL it is possible to reduce updating of the majority of reports before simple clicking of the Update button:
1. Data from any quantity of sources are imported through SQL queries to the normal tables Excel.
2. Even from big base it is possible to write in Excel only small part of data (for example, total sums for the necessary range of dates with grouping only on the necessary columns).
3. Changes can be made to the report just having changed the SQL query. Further we create the necessary report standard means of Excel.

I will show how to configure and autocomplete the simple MySQL databases (on the example of unloading of statistics of all key word from Metrics Yandex) in this article, and then one button to update reports in Excel, using Power Query superstructure. Power Query has very strange features of work by drawing up SQL queries (especially dynamic) which we will sort in the second part of article.

Read more »


PowerShell, AWS CLI and json

1 year ago
During the work with Amazon cloud, often it is necessary to execute many routine operations through Web the console. But there is a wish to automate them.
AWS CLI, the command line interface, well for this purpose approaches. Of course, it is possible to write also the application on Scala, but in daily tasks it is better oboytsya without "heavy artillery".
The AWS commands are able to return data in different formats including in json. It is possible to use bash and jq, but there is no last in cygwin repository, and hands to set laziness. Meanwhile in PowerShell there is a fine support of json! The truth it turned out that it is not absolutely simple to use it.

Read more »


Facebook threatens the security expert who cracked Instagram

1 year, 1 month ago
The independent security expert Wesley Weinberg (Wesley Wineberg) underwent serious pressure from the Facebook company. She did not pay for the found vulnerabilities on the Instagram server moreover threatens with a legal claim.

Wesley found vulnerability in infrastructure of Instagram by means of which downloaded practically all valuable that is on the Instagram servers: the source code of the latest version, SSL certificates and private keys for Instagram.com, keys for the signature of kuk of authentication, accounting data from the e-mail server and keys for some other products, including for the signature of mobile applications under iOS and Android.


Read more »


Experience of sending Apple Push Notification a cher SNS service from Amazon and a little useful code

1 year, 2 months ago
In the warm summer evening, in development process of the next application for iOS, at me two factors matched — there was a need of implementation of Apple Push Notification (APN) and desire to try something absolutely new to me. There was no wish to go the blazed road through one of a set of the services offering sending APN.

For the invention of the bicycle Amazon Simple Notification Service (Amazon SNS) was selected. Amazon SNS is a service which allows you to send messages notifications, by means of various mechanisms (APN, GCM, e-mail, the SMS, etc.).

How it works and more detailed information it is possible to read in documentation of Amazon. I will tell very little to decide on further terminology. SNS have two types of clients — publishers (publishers) and subscribers (subscribers). Publishers asynchronously communicate with subscribers (messages) which are delivered to subscribers, by means of various mechanisms. For sending block messages subscribers can be grouped in subjects (topics). Then all subscribers signed on a subject will receive the message in this subject sent.

The picture from documentation of Amazon:
image

Read more »


SparkleFormation — the CloudFormation generator of templates with rainbows and unicorns

1 year, 2 months ago
SparkleFormation

If you seriously use AWS (Amazon Web Services), then for certain know about an opportunity to describe infrastructure by means of JSON templates. In AWS this service is called CloudFormation. In fact this solution allows you to describe a desirable status of any resources available in AWS (instansa, layers of opsworks, ELB, security groups, etc.). The set of resources is called a stack. After loading of CloudFormation of a template the system itself or will create necessary resources in a stack if they are absent yet, or will try to update existing to a desirable status.

It well works if you have a small amount of resources, but as soon as the infrastructure expands problems appear:
  • In JSON there is no opportunity to use cycles and for similar resources it is necessary to repeat the same parameters and in case of change too (not DRY)
  • For the configuration record for cloud-init double escaping is necessary
  • In JSON there are no comments and it has a bad cheloveko-chitayemmost

To avoid similar problems engineers from Heavy Water wrote on ruby DSL and CLI for generation and work with these templates under the name SparkleFormation (github).

Read more »


Infrastructure of Amazon Web Services from within. Part 2

1 year, 3 months ago


The Amazon company, to be exact, at its cloudy division has AWS — one of the most quickly developing, unrolled and perfect infrastructures. As we already wrote, not so long ago the company has opened some secrets concerning the principles of the organization of work of this infrastructure. In the second part — information on where data-centers how all this is connected in one system are placed and as works.

Now at the disposal of Amazon Web Services — at least 30 data-centers, and the manual is going to construct in addition 10 or 15 new. The majority of data-centers are placed in northern Virginia, here about 20 data-centers are responsible for work of AWS, with a total power about 500 MW. But the geography of infrastructure of data-centers of AWS is not limited only to Virginia. Now three large campuses of DTs are under construction in Ohio, plus cloudy data-centers work in Ireland, Brazil, China, Japan, Australia and Singapore.

Read more »


As the flood in Thailand in 2011 has helped Amazon Web Services to rethink the work and to create "cloudy small suitcase"

1 year, 3 months ago


There is a lot of clouds in Thailand, and it not figurative expression — here really often are rains, especially to season of monsoons. Most actively the nature has proved in 2011 when in Thailand there was large-scale flood caused by heavy rains incessant for a long time. On Thailand, as we know, there are a lot of factories on production of hard drives and drives of other types — and just these factories, in the majority, have also been flooded in 2011. Then the prices of hard drives raised, chains of deliveries of winchesters by that who needs them, have been broken.

Cloudy subsection of the Amazon company has experienced on itself influence of elements more, than many other companies. So, data-centers of Amazon Web Services for some time remained without the necessary equipment that has brought to some to problems in work of AWS. The vice-president of AWS for infrastructure Gerry Hunter has told about all this on recent action of "re:Invent".

Read more »


How to pogrepat the Internet

1 year, 3 months ago
Analysts sometimes need to answer questions like such: "how many the sites use WordPress, and how many Ghost", "what covering at Google Analytics and what at the Metrics", "as often the site of X refers to the site Y". The most honest way to answer them — to walk on all pages on the Internet and to count. This not so mad idea as can seem. There is Commoncrawl project which publishes every month fresh dump of the Internet in the form of gzip-archives the total size in ~ 30tb. Data lie on S3 therefore for processing MapReduce from Amazon is usually used. There is mass of instructions how to do it. But with current rate of dollar such approach became a little expensive. I would like to share in the way how to reduce the price of calculation approximately twice.

Read more »


How to optimize work of online store. Part 1. Automatic pro-ring of the entering leads

1 year, 4 months ago
In week after output of the previous article where I told about assembly of bot for Telegram, the online store of watch which by that moment has already constructed some processes for the needs has addressed to me for consultation. With business growth they have faced problem of processing of large number of the entering requests, and on fast reaction to the request depends, the person will buy product or not.

Here I will describe their case of optimization of processes of online store by means of Corezoid.com and I will give the reference to template of the process described below in open access.

That it is necessary to make:
1) To add lead to CRM (in this case it is Bitrix24) with filling of all necessary additional fields.
2) To notify sellers on the come lead or error.
3) To notify the client on successfully taken order, and also to tell about additional action (to try to warm up the client, to raise the average check).

On input we have:
1) Name and phone of the client who has interested our goods.
2) The city of the client determined by ip.
3) To Taymzon of the client.
4) The partner's identifier (the ref-link in cookies) from which the request has come.

Solution became linking of API of several services through Corezoid.com, namely: Bitrix24 (CRM-system) + VoxImplant (service for callback implementation) + Telegram (messenger).

Read more »


Microservices without servers (cloudy option)

1 year, 4 months ago
Hello, dear readers!

As remember the most attentive of you, we work on the innovation book about microservices, but at the same time we do not forget to read that write to Networks on this subject. Therefore could not pass by the most interesting article from the blog Amazon which author Tim Wagner shares the views of microservice architecture.

It is a lot of Java, JavaScript, schemes, reasonings and it is a lot of text

Read more »